Sign in to follow this  
Followers 0
gooker

VB TO AU3,HELP?

5 posts in this topic

#1 ·  Posted (edited)

IT'S ABOUT ACCESS TO EXCEL.

Public Function ExportTableToOneExcel(ByVal AccName As String, ByVal ExpExcelName As String, Optional tableName As String = "", Optional DbPassWord As String = "")
    Dim i As Long
    Dim rstSchema As ADODB.Recordset
    Dim accApp As Access.Application
    Dim cnnTemp As ADODB.Connection
    Set accApp = New Access.Application
    accApp.OpenCurrentDatabase AccName, , DbPassWord 
    Set cnnTemp = accApp.CurrentProject.Connection  
    Set rstSchema = cnnTemp.OpenSchema(adSchemaTables)
    Do Until rstSchema.EOF
        If rstSchema("TABLE_TYPE") = "TABLE" Then
            For i = 0 To rstSchema.Fields.Count - 1
                If rstSchema(i).Name = "TABLE_NAME" Then 
                    If tableName <> "" Then 
                        If rstSchema.Fields(i).Value = tableName Then
                            accApp.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, tableName, ExpExcelName, True
                            Exit Do
                        End If
                    Else 
                        accApp.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, rstSchema.Fields(i).Value, ExpExcelName, True
                    End If
                End If
            Next
        End If
        rstSchema.MoveNext
    Loop
    rstSchema.Close
    Set cnnTemp = Nothing
    accApp.CloseCurrentDatabase
    Set accApp = Nothing
End Function
Edited by gooker

Share this post


Link to post
Share on other sites



AutoIt is more like VBScript that VB (or VB.NET), but here's an untested shot at it:

Func ExportTableToOneExcel($AccName, $ExpExcelName, $tableName = "", $DbPassWord = "")
    Local $i, $rstSchema, $accApp, $cnnTemp
    Local $adSchemaTables, $acExport, $acSpreadsheetTypeExcel9 ; <-- Where are these set???
    $accApp = ObjCreate("Access.Application")
    $accApp.OpenCurrentDatabase($AccName, "", $DbPassWord)
    $cnnTemp = $accApp.CurrentProject.Connection
    $rstSchema = $cnnTemp.OpenSchema($adSchemaTables) 
    Do
        If $rstSchema("TABLE_TYPE") = "TABLE"  Then
            For $i = 0 To $rstSchema.Fields.Count - 1
                If $rstSchema($i).Name = "TABLE_NAME"  Then
                    If $tableName <> "" Then
                        If $rstSchema.Fields($i).Value = $tableName Then
                            $accApp.DoCmd.TransferSpreadsheet($acExport, $acSpreadsheetTypeExcel9, $tableName, $ExpExcelName, True)
                            ExitLoop
                        EndIf
                    Else
                        $accApp.DoCmd.TransferSpreadsheet($acExport, $acSpreadsheetTypeExcel9, $rstSchema.Fields($i).Value, $ExpExcelName, True)
                    EndIf
                EndIf
            Next
        EndIf
        $rstSchema.MoveNext
    Until $rstSchema.EOF
    $rstSchema.Close
    $cnnTemp = ""
    $accApp.CloseCurrentDatabase
    $accApp = ""
EndFunc   ;==>ExportTableToOneExcel

I had no idea what to make of these variables: $adSchemaTables, $acExport, $acSpreadsheetTypeExcel9

Probably won't work, but might be a starting point.

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

AutoIt is more like VBScript that VB (or VB.NET), but here's an untested shot at it:

Func ExportTableToOneExcel($AccName, $ExpExcelName, $tableName = "", $DbPassWord = "")
    Local $i, $rstSchema, $accApp, $cnnTemp
    Local $adSchemaTables, $acExport, $acSpreadsheetTypeExcel9 ; <-- Where are these set???
    $accApp = ObjCreate("Access.Application")
    $accApp.OpenCurrentDatabase($AccName, "", $DbPassWord)
    $cnnTemp = $accApp.CurrentProject.Connection
    $rstSchema = $cnnTemp.OpenSchema($adSchemaTables) 
    Do
        If $rstSchema("TABLE_TYPE") = "TABLE"  Then
            For $i = 0 To $rstSchema.Fields.Count - 1
                If $rstSchema($i).Name = "TABLE_NAME"  Then
                    If $tableName <> "" Then
                        If $rstSchema.Fields($i).Value = $tableName Then
                            $accApp.DoCmd.TransferSpreadsheet($acExport, $acSpreadsheetTypeExcel9, $tableName, $ExpExcelName, True)
                            ExitLoop
                        EndIf
                    Else
                        $accApp.DoCmd.TransferSpreadsheet($acExport, $acSpreadsheetTypeExcel9, $rstSchema.Fields($i).Value, $ExpExcelName, True)
                    EndIf
                EndIf
            Next
        EndIf
        $rstSchema.MoveNext
    Until $rstSchema.EOF
    $rstSchema.Close
    $cnnTemp = ""
    $accApp.CloseCurrentDatabase
    $accApp = ""
EndFunc   ;==>ExportTableToOneExcel

I had no idea what to make of these variables: $adSchemaTables, $acExport, $acSpreadsheetTypeExcel9

Probably won't work, but might be a starting point.

:P

ths

:)


Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

@all

Just to fill in the missing parts.

Func ExportTableToOneExcel($AccName, $ExpExcelName, $tableName = "", $DbPassWord = "")
    Const $adSchemaTables = 20
    Const $acExport = 1
    Const $acSchemaXSD = 1
    Const $acSpreadsheetTypeExcel9 = 8
    
    Local $i, $rstSchema, $accApp, $cnnTemp
    Local $adSchemaTables, $acExport, $acSpreadsheetTypeExcel9 ; <-- Where are these set???
    $accApp = ObjCreate("Access.Application")
    $accApp.OpenCurrentDatabase($AccName, "", $DbPassWord)
    $cnnTemp = $accApp.CurrentProject.Connection
    $rstSchema = $cnnTemp.OpenSchema($adSchemaTables) 
    Do
        If $rstSchema("TABLE_TYPE") = "TABLE"  Then
            For $i = 0 To $rstSchema.Fields.Count - 1
                If $rstSchema($i).Name = "TABLE_NAME"  Then
                    If $tableName <> "" Then
                        If $rstSchema.Fields($i).Value = $tableName Then
                            $accApp.DoCmd.TransferSpreadsheet($acExport, $acSpreadsheetTypeExcel9, $tableName, $ExpExcelName, True)
                            ExitLoop
                        EndIf
                    Else
                        $accApp.DoCmd.TransferSpreadsheet($acExport, $acSpreadsheetTypeExcel9, $rstSchema.Fields($i).Value, $ExpExcelName, True)
                    EndIf
                EndIf
            Next
        EndIf
        $rstSchema.MoveNext
    Until $rstSchema.EOF
    $rstSchema.Close
    $cnnTemp = ""
    $accApp.CloseCurrentDatabase
    $accApp = ""
EndFunc   ;==>ExportTableToOneExcel

regards

ptrex

Edited by ptrex

Share this post


Link to post
Share on other sites

@all

Just to fill in the missing parts.

Func ExportTableToOneExcel($AccName, $ExpExcelName, $tableName = "", $DbPassWord = "")
    Const $adSchemaTables = 20
    Const $acExport = 1
    Const $acSchemaXSD = 1
    Const $acSpreadsheetTypeExcel9 = 8
    
    Local $i, $rstSchema, $accApp, $cnnTemp
    Local $adSchemaTables, $acExport, $acSpreadsheetTypeExcel9 ; <-- Where are these set???
    $accApp = ObjCreate("Access.Application")
    $accApp.OpenCurrentDatabase($AccName, "", $DbPassWord)
    $cnnTemp = $accApp.CurrentProject.Connection
    $rstSchema = $cnnTemp.OpenSchema($adSchemaTables) 

    ; ...

EndFunc   ;==>ExportTableToOneExceloÝ÷ Ú· j·l¦ÚÞÇú®¢×¢{-j{lßt¡¢H².ØZ½é'£  í¢Z(ú+¶­ëh"ÚÞ}êÞÇ®®

It might actually work now!

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0