467,074 Members | 926 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,074 developers. It's quick & easy.

Problem in exporting access table data to an excel file

100+
Hi! I have exported an access table data into an excel file using the following code. However, the code exported all the data to the excel sheet starting from 'A' cell (first column). But I want to export all the data into the excel starting from cell B (second column) and include the heading "Extract Policy" followed by checkboxes in 'A' cell.

Can anyone suggest me how to do this? Thanks a million in advance.

Expand|Select|Wrap|Line Numbers
  1. Function ExportTableToExcel()
  2. 'Export function
  3. 'EXPORTS TABLE IN ACCESS DATABASE TO EXCEL
  4. 'REFERENCE TO DAO IS REQUIRED
  5.  
  6. Dim strExcelFile As String
  7. Dim strWorksheet As String
  8. Dim strDB As String
  9. Dim strTable As String
  10. Dim objDB As Database
  11.  
  12. strExcelFile = "C:\ExtractPolicyList.xls"
  13. strWorksheet = "WorkSheet1"
  14. strDB = "C:\Example1.mdb"
  15. strTable = "ImportedData"
  16.  
  17. Set objDB = OpenDatabase(strDB)
  18.  
  19. 'If excel file already exists, you can delete it here
  20. If Dir(strExcelFile) <> "" Then Kill strExcelFile
  21.  
  22. objDB.Execute _
  23. "SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
  24. "].[" & strWorksheet & "] FROM " & "[" & strTable & "]"
  25. objDB.Close
  26. Set objDB = Nothing
  27. End Function
Jul 16 '08 #1
  • viewed: 2196
Share:
1 Reply
Expert 256MB
You want to check from:
Expand|Select|Wrap|Line Numbers
  1. objDB.Execute _
  2. "SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
  3. ".[" & strWorksheet & "] FROM " & "[" & strTable & "]"
TO:

Expand|Select|Wrap|Line Numbers
  1. objDB.Execute _
  2. "SELECT <<Name the fields that you want to select>> INTO [Excel 8.0;DATABASE=" & strExcelFile & _
  3. ".[" & strWorkSheet & "] FROM [" & strTable & "]"
Replace <<Name the fields that you want to select>> with the actual names of the fields that you want to export separating each field name with a comma ","

example

select Name, address, Phone From TableName

Hope that helps,

Joe P.
Jul 17 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Chris | last post: by
21 posts views Thread by bobh | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.