Problem with Docmd.Transferspreadsheet command

[sranilp]

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

[JConsulting]

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

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Inventory_Acc", "C:\Inventory.xls", True, "Raw Data"
 

[sranilp]

This should work for you
J

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Inventory_Acc", "C:\Inventory.xls", True, "Raw Data"
 

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

[JConsulting]

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

Function ExportToExcel()
Dim xl As Excel.Application
Dim wkbk As Excel.Workbook
Dim sht As Excel.Worksheet, rng As Excel.Range
Dim db As DAO.DataBase, rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from YourSource;")  '<--Your Table or Query here
Set xl = CreateObject("Excel.Application")
Set wkbk = xl.Workbooks.Open("C:\YourSpreadsheet.xls")  '<--Your Excel File Here
xl.Visible = True
Set sht = wkbk.Sheets("YourSheet")                      '<--Your Sheet Here
With sht
.Range("A1").CopyFromRecordset rs                       '<--start at any range you want here
End With
rs.Close
wkbk.Close (True)
xl.Quit
Set xl = Nothing
End Function