Exporting to Excel by column 1

[3576]

I was wondering if it is possible to export from an access database to an excel spreadsheet column by column (skipping some in the middle) using Visual basic commands?

 

[Swi]

You could use ADO.

Private Sub Command1_Click()
' Add a reference to Microsoft ActiveX Data Objects X.X Library
Dim conn As New ADODB.Connection
Dim numofrecscopied As Long
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\YOURACCESSDBNAME.mdb"
conn.Open
conn.Execute "SELECT FIELD1, FIELD10, FIELD20 INTO [Excel 8.0;" & _
"Database=C:\YOUREXCELFILENAME.XLS].[Sheet1] FROM " & _
"YOUR ACCESS TABLE NAME", numofrecscopied
conn.Close
Set conn = Nothing
MsgBox numofrecscopied & " records copied to Excel!", vbInformation
End Sub

Be aware that using this method will limit the output to the Excel sheet to 65,536 since that is the max cells for a sheet. If you want to span multiple sheets you could dump you data to a ADO recodset and use Excel automation to put your data in Excel.

Swi

 

[3576]

Thank you very much for your reply. What I really need to do is to transfer certain columns from the table into an excel spreadsheet. The column name in the spreadsheet are not in the same order so I need to check for matching column name and then transfer the data. I was hoping that it is possible to use recordset and transfer data by column instead of a record. I know that I can transfer cell by cell but that takes a lot of time to do so. Thanks again for your help.

 

[Swi]

You could always use check to see if the fields exist and then use my above code to SELECT the columns in the order you want. Here is a link to a thread that has an example.

thread222-846120

Swi