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.
Code:
[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.
Code:
"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]
Code:
'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.
