467,158 Members | 1,008 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

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

Problem with Docmd.Transferspreadsheet command

Hey All,

Actually I need to export the data from Access to Excel particular spreadsheet(ie.Raw Data),so I was using Docmd.Transferspreadsheet but in this syntax where i can give the spreadsheet name.

Example:

I have a Excelfile name is Inventory.xls,and sheet name is 'Raw Data',so i need Access data should export to Inventory Excel file Raw Data sheet.

I tried like this:'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Inventory_Acc", "C:\Inventory.xls", True

So where I have to mention the Sheet name.Above format only exports the data to Excelfile.

Please help on this.

Thanks,
Phani
Jul 12 '07 #1
  • viewed: 3992
Share:
3 Replies
JConsulting
Expert 512MB
Hey All,

Actually I need to export the data from Access to Excel particular spreadsheet(ie.Raw Data),so I was using Docmd.Transferspreadsheet but in this syntax where i can give the spreadsheet name.

Example:

I have a Excelfile name is Inventory.xls,and sheet name is 'Raw Data',so i need Access data should export to Inventory Excel file Raw Data sheet.

I tried like this:'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Inventory_Acc", "C:\Inventory.xls", True

So where I have to mention the Sheet name.Above format only exports the data to Excelfile.

Please help on this.

Thanks,
Phani
This should work for you
J
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Inventory_Acc", "C:\Inventory.xls", True, "Raw Data"
  2.  
Jul 12 '07 #2
This should work for you
J
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Inventory_Acc", "C:\Inventory.xls", True, "Raw Data"
  2.  

Hi

Thanks for reply,but this one not working iam getting error as "The Microsoft Jet Engine could not found the object "Raw Data".So could please help on this..

Thanks,

Anil
Jul 13 '07 #3
JConsulting
Expert 512MB
Hey All,

Actually I need to export the data from Access to Excel particular spreadsheet(ie.Raw Data),so I was using Docmd.Transferspreadsheet but in this syntax where i can give the spreadsheet name.

Example:

I have a Excelfile name is Inventory.xls,and sheet name is 'Raw Data',so i need Access data should export to Inventory Excel file Raw Data sheet.

I tried like this:'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Inventory_Acc", "C:\Inventory.xls", True

So where I have to mention the Sheet name.Above format only exports the data to Excelfile.

Please help on this.

Thanks,
Phani

Give this a try

Expand|Select|Wrap|Line Numbers
  1. Function ExportToExcel()
  2. Dim xl As Excel.Application
  3. Dim wkbk As Excel.Workbook
  4. Dim sht As Excel.Worksheet, rng As Excel.Range
  5. Dim db As DAO.DataBase, rs As DAO.Recordset
  6. Set db = CurrentDb
  7. Set rs = db.OpenRecordset("Select * from YourSource;")  '<--Your Table or Query here
  8. Set xl = CreateObject("Excel.Application")
  9. Set wkbk = xl.Workbooks.Open("C:\YourSpreadsheet.xls")  '<--Your Excel File Here
  10. xl.Visible = True
  11. Set sht = wkbk.Sheets("YourSheet")                      '<--Your Sheet Here
  12. With sht
  13. .Range("A1").CopyFromRecordset rs                       '<--start at any range you want here
  14. End With
  15. rs.Close
  16. wkbk.Close (True)
  17. xl.Quit
  18. Set xl = Nothing
  19. End Function
  20.  
Jul 13 '07 #4

Post your reply

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

Similar topics

2 posts views Thread by mcnewsxp | last post: by
5 posts views Thread by Gary Cobden | last post: by
4 posts views Thread by Tom Louchbaum | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.