Submitted By: Anonymous Submission

Exports Microsoft Access tables to a Microsoft Excel spreadsheet.

Visual Basic
Edit|Remove
dim  commandstring 
 
dim tables_in_array(3) 

Set objConnection = CreateObject("ADODB.Connection")
 
tables_in_array(1) = "table1"
tables_in_array(2) = "table2"
tables_in_array(3) = "table3" 

objConnection.Open _
                  "Provider = Microsoft.Jet.OLEDB.4.0; " & _
                  "Data Source = c:\my_directory\my_database.mdb"
 
for i = 1 to 3
     
          input_tablename = tables_in_array(i)
     
	  commandstring  = "SELECT * INTO [Excel 8.0;HDR=Yes;" & _
                "Database=c:\my_directory\" & _
                       input_tablename & ".xls].Sheet1 FROM " & input_tablename     
	  objConnection.Execute commandstring
next
 
msgbox ("finished exporting all TABLEs")
 
 
objConnection.Close