Export access to excel in vb6

[kennedymr2]

Is there a simple way of exporting a table in an access2000 database, to a new excel spreadsheet, from a command button in a vb6 form.
i can see lots of solutions but they all seem complicated. I only need to export and end up with the same field names etc.


Appreciate any help

kennedymr2

 

[johnwm]

The easy way of doing most of the 'Office' interactions from VB is to open the target app (Excel in this case) and record a macro from Tools|Macro|Record a new macro.

Then go through the process of importing the database:
Data|Import External Data|Import Data, and follow the wizard.

Stop the macro and use the code generated as the basis for your VB code. You will need to change references to the Application object of course, and edit out some of the excess parameters passed, but the main bits of your code are all done.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[bjd4jc]

Also if you use the access object model then you can utilize the transfer spreadsheet method:

Dim accApp as Access.Application

Set accApp = New Access.Application
accApp.OpenCurrentDatabase "path here"
accApp.TransferSpreadsheet "don't know parameters off the top of my head, just look at them and they will make sense"

 

[dilettante]

Another technique uses ADO and does not require that the machine where your program runs have either Access or Excel installed. It does require MDAC 2.1 or later and Jet 4.0, but you'd probably have these already or your VB program would not be working with an MDB anyway.

It has a few limitations:
[ul][li]It cannot create a new Excel Workbook. This means you need an empty one to copy, then load your MDB table data into.[/li]
[li]This also means you'll end up with at least one empty unused Worksheet in the Workbook. Excel can't save a "blank" Workbook without at least one Worksheet in it. ADO cannot delete a Worksheet that it hasn't created.[/li]
[li]You cannot control Column formatting such as font, width, etc. from ADO.[/li][/ul]

Within those limits though, it isn't too bad.

This small example with no error checking creates an MDB with one Table from a text (CSV) file. It then creates an XLS Workbook with one Workshaeet from that Table. It requires 3 files:

[tt]Schema.ini[/tt] a Jet Text schema file describing the CSV file.

[Stuff.txt]
Format=CSVDelimited
ColNameHeader=False
Col1="Customer Name" Text Width 36
Col2="Customer Number" Long
Col3=New Bit

[tt]Stuff.txt[/tt] the CSV data file.

"Jones, Jim",2321,False
"Williams, William",4323,True
"Sandstone, Sam",2208,False
"Jensen, Sally",4082,True
"Abrahim, Samar",3956,True

[tt]Blank.xls[/tt] a blank (one empty Worksheet) Excel Workbook.


Then there is the program itself. This is a VB EXE project with one standard code module:

[tt]modMain.bas[/tt]

'Create an MDB table from a Text (CSV) file,
'then create an XLS sheet from the MDB table.
'
'Requires references to:
'
'  Microsoft Scripting Runtime
'  Microsoft ActiveX Data Objects 2.x Library
'  Microsoft ADO Ext. 2.x for DDL and Security
'
'    ... where 2.x = 2.1 or later.
'

Const APP_DATA_PATH As String = ""
Const DB_DATA_PATH As String = "Data"

Const DB_PROVIDER As String = "Provider=Microsoft.Jet.OLEDB.4.0;"

Const DB_TEXT As String = "Extended Properties=""Text"";"
Const DB_TEXTFILE As String = "Stuff.txt"

Const DB_ACCESS2000 As String = "Jet OLEDB:Engine Type=5;"
Const DB_MDBFILE As String = "Stuff.mdb"
Const DB_TABLE As String = "Stuff"

'Excel 8.0 is used for both Excel 97 and 2000.
Const DB_EXCEL2000 As String = _
                    "Extended Properties=""Excel 8.0;HDR=Yes"";"
Const DB_BLANK_XLSFILE As String = "Blank.xls"
Const DB_XLSFILE As String = "Stuff.xls"
Const DB_SHEET As String = "Stuff"

Dim FSO As New Scripting.FileSystemObject

Function FullPath() As String
    Dim strDataPath As String
    
    If Len(APP_DATA_PATH) = 0 Then
        strDataPath = App.Path
    Else
        strDataPath = APP_DATA_PATH
    End If
    
    FullPath = strDataPath & "\" & DB_DATA_PATH
End Function

Function QFullPath() As String
    QFullPath = """" & FullPath() & """"
End Function

Function FilePath(ByVal File As String) As String
    FilePath = FullPath() & "\" & File
End Function

Function QFilePath(ByVal File As String) As String
    QFilePath = """" & FilePath(File) & """"
End Function

Sub MakeMDB()
    Dim CAT As ADOX.Catalog
    Dim CMD As ADODB.Command
    
    If FSO.FileExists(FilePath(DB_MDBFILE)) Then
        FSO.DeleteFile FilePath(DB_MDBFILE), _
                       True
    End If
        
    Set CAT = New ADOX.Catalog
    CAT.Create DB_PROVIDER & DB_ACCESS2000 _
             & "Data Source=" & QFilePath(DB_MDBFILE)
    Set CAT = Nothing
            
    Set CMD = New ADODB.Command
    CMD.ActiveConnection = DB_PROVIDER & DB_TEXT _
                         & "Data Source=" & QFullPath()
    CMD.CommandText = _
        "SELECT * INTO [" & DB_TABLE & "]" _
      & " IN " & QFilePath(DB_MDBFILE) _
      & " FROM [" & DB_TEXTFILE & "]"
    
    CMD.Execute , , adCmdText Or adExecuteNoRecords
    Set CMD = Nothing
End Sub

Sub MakeXLS()
    Dim CMD As ADODB.Command
    
    FSO.CopyFile FilePath(DB_BLANK_XLSFILE), _
                 FilePath(DB_XLSFILE), _
                 True

    Set CMD = New ADODB.Command
    CMD.ActiveConnection = DB_PROVIDER & DB_EXCEL2000 _
                         & "Data Source=" & QFilePath(DB_XLSFILE)
    CMD.CommandText = _
        "SELECT * INTO [" & DB_SHEET & "]" _
      & " FROM [" & DB_TABLE & "]" _
      & " IN " & QFilePath(DB_MDBFILE) _
      & " WHERE New = True AND [Customer Number] >= 4000"

    CMD.Execute , , adCmdText Or adExecuteNoRecords
    Set CMD = Nothing
End Sub

Sub Main()
    MakeMDB
    MakeXLS
End Sub

As shown here, this program expects the three required files to be in a directory [tt]Data[/tt] within the directory holding the EXE itself. New files will be created in [tt]Data[/tt] as well:

[tt][some dir]
|
+-- sample.exe
|
+-- [Data]
|
+-- Schema.ini
|
+-- Stuff.txt
|
+-- Blank.xls
|
+-- Stuff.mdb [/tt]to be created[tt]
|
+-- Stuff.xls [/tt]to be created


This program does a little more than you need. There is nothing here that prevents the code from being used in a VB forms program either.

[tt]MakeMDB[/tt] deletes any existing MDB file [tt]Stuff.mdb[/tt] then creates an empty MDB file of the same name. Then it imports the text file [tt]Stuff.txt[/tt] into the MDB file as the Table [tt]Stuff[/tt].

[tt]MakeXLS[/tt] copies the XLS file [tt]Blank.xls[/tt] as [tt]Stuff.xls[/tt] overwriting any existing file of the same name. Then it imports the Table [tt]Stuff[/tt] from [tt]Stuff.mdb[/tt] into the new XLS file as the Worksheet [tt]Stuff[/tt].

In your case you already have a database, so the first part is extraneous. It just makes it easy to test this sample program.


If you can't live with the limitations described above, there are a couple of options.

I haven't worked with [tt]TransferSpreadsheet[/tt] but it may present many of the same limitations as my ADO approach. In particular, I suspect that Column formatting will be an issue.

Another possibility is to automate an instance of Excel. Use [tt].Workbooks.Add[/tt] to create a new Workbook, etc. Then open the MDB Table into a Recordset and use the Excel object's range[tt].CopyFromRecordset[/tt] method to get the data into the Worksheet. While you have the Worksheet open via this automated Excel instance you can format the Columns as you choose.

Both of those ways require extra software (Access and Excel, respectively) on the machine running your VB program. Neither one is recommended server-side, but since you mention clicking on a VB form's button I assume this is for some desktop application.

 

[kennedymr2]

Thanks very much for all the help offered.
I can see there are lots of ways of carrying out this procedure.

Will give all the above ideas a run.

Once again, appreciate the help very much.

regards kennedymr2

 

[kennedymr2]

Have tried all the above methods all seem to work fine.
As i only need to transfer the raw data to a spreadsheet, the transferspreadsheet method seems to be the easy way.

Really appreciate all the help offered.

Regards Kennedymr2