By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,081 Members | 861 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,081 IT Pros & Developers. It's quick & easy.

Problem with Docmd.Transferspreadsheet command

P: 10
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
Share this Question
Share on Google+
3 Replies


JConsulting
Expert 100+
P: 603
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

P: 10
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 100+
P: 603
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.