VB TO AU3,HELP?

[gooker]

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 January 3, 2008 by gooker

[PsaltyDS]

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.

[gooker]

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.

ths

[ptrex]

@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 January 4, 2008 by ptrex

[PsaltyDS]

@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!