DoCmd.TransferSpreadsheet Problem

[mjmact]

I have been trying to get DoCmd.TransferSpreadsheet to work in my vba code. and it refuses to work how I need it to. Maybe my syntax is wrong, or something, but I can't figure it out.

I have

Function Copy_to_excel
File_Name = Get_Temp_Data_fn("File_Path")
Sheet_Name = Get_Temp_Data_fun("Sheet_Name")
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Concatenated", File_Name!Sheet_Name, True, ""
End Function

Get_Temp_Data_fun("File_Path") returns the excel sheet I am exporting to w/ full path. i.e. "C:\Documen... ...Test Data.xls"
Get_Temp_Data_fun("Sheet_Name") returns the name of the spreadsheet I want the sheet to be transfered as (what it should be in the excel file) i.e. "Test_Sheet"
"Concatenated" is the name of the query I am trying to send to excel.

I saw doing the File_Name!Sheet_Name somewhere, but can't make it work. It returns a Run-time error ‘424: Object Required. Using Just File_Name works fine, however, I need to make it output with a different sheet name everytime, and I will not know what the sheet name is before hand (the user is selecting a different sheet to use every time)

Any thoughts on what I should change to get this to work correctly?

Thanks for any help.

 

[Joe4]

I have no idea if you can get it to work like the way you want using this methodology (export to a specific spreadsheet), but assuming you can (and that is a big assumption), at the very least you need to change the variable call from:

File_Name!Sheet_Name

to

File_Name & "!" & Sheet_Name

Based on the way you have written it, Access if looking for a variable named File_Name!Sheet_Name, which obviously doesn't exist (you have two separate variables).

 

[mjmact]

Thanks for your response.

Like I said, I saw that code somewhere (not on here) or at least something similar. Making the change you suggested gave a different error...
Run-time error '3027': Cannot update. Database or object is read-only.
You said you weren't sure if I could do what I am trying using this methodology. Is there another method that would achieve the same results that you (or anyone else) knows of?

 

[Joe4]

Have a look at Access' help on the TransferSpreadsheet command:

Quote form Access help:

File Name:
The name of the spreadsheet file to import from, export to, or link to. Include the full path. This is a required argument.
Microsoft Access creates a new spreadsheet when you export data from Microsoft Access. If the file name is the same as the name of an existing spreadsheet, Microsoft Access replaces the existing spreadsheet, unless you're exporting to a Microsoft Excel version 5.0, 7.0, 8.0 or Excel 2000 workbook. In that case, Microsoft Access copies the exported data to the next available new worksheet in the workbook.
If you are importing from or linking to a Microsoft Excel version 5.0, 7.0, 8.0 or Excel 2000 spreadsheet, you can specify a particular worksheet by using the Range argument.

Notice the last line. It looks like you might be able to use the Range argument of the function to specify which worksheet you want to send it to.

 

[mjmact]

Hmm. I DID apparently miss that when I was reading, however, after looking at it a bit I don't believe that would work. Thats for an import or a link (it seems as if linking to get data from excel). In the part of the help about the range, it says to keep it blank if it is for an export, or else it will fail. Either way, when I change the range value from "" to a range with a spreadsheet name in front of it with the ! it still gives me an error.
Is there possibly a way to rename a sheet in an excel file from access (move it as the name of the query since that works, and just rename the sheet once it is in excel using Access)

Thanks

 

[WmColwell]

Specifying the sheet name...

Found this thread researching a similar problem.

I think you can select the sheet by using the "range" parameter. For example:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TableToExport", "C:\ExcelFile.xls", False, "TheTabName$"

Hope this helps.

 

[slipstream]

Re: Specifying the sheet name...

I've used this code before exporting specific fields in to named ranges on an existing spreadsheet, not sure if this will help you any ?

Private Sub Command21_Click()

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open("c:\pathname\file.xlt")
Set oSheet = oBook.Worksheets("Sheet1")

oExcel.Visible = True

With oSheet
 .Range("JobNo").Value = JobNo
 .Range("CustRef").Value = Customer & " / " & Reference & " / " & RoofRNumber
 .Range("DeliveryAdd").Value = DeliveryAdd
 .Range("RemDate").Value = [Remedials.OrderDate]
 .Range("SupplierReference").Value = SupplierReference
 .Range("DateDel").Value = [RoofOrder.DateReceived]
 .Range("DateReq").Value = [Remedials.DateReq]
 .Range("SpecialNotes").Value = [Remedials.DateReqNotes]
 .Range("Reason").Value = ReasonReq
 
End With

End Sub

 

[Mazatlanmexico]

I use this code a lot to transfer data both ways between Excel and Access. I hope this will help you get ideas.

Private Sub ExportToExcel_Click()
'Set a reference to Excel 11.0

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set dbs = CurrentDb
Dim fld As Variant
Dim a As Variant


Set rst = dbs.OpenRecordset("YourRecordsetName")
Set xlApp = CreateObject("Excel.Application")

If rst.RecordCount <= 1 Then
MsgBox "There's not enough records to transfer to Excel"
Exit Sub
End If

 'Open the spreadsheet to which you Want to export the data.
'and Find the Excel workbook where the current database is.

Set xlBook = xlApp.Workbooks.Open(CurrentProject.Path & "\YourWorkbookName.xls")
xlBook.Worksheets(1).Select

'Delete old Data
xlApp.Visible = False   'Set this to True if you want to see the workbook.
xlApp.Range("YourStartRangeHere").CurrentRegion.ClearContents

xlApp.[A5].CopyFromRecordset rst    'Replace A5 with your own range
For Each fld In rst.Fields
a = a + 1
xlApp.Cells(4, a).Value = fld.Name  'Replace 4 with your row number
Next fld
xlApp.ActiveSheet.Columns.AutoFit
xlApp.Rows(4).Font.Bold = True   'Replace 4 with your row number
xlApp.Rows(5).Select              'Replace 5 with your row number
xlApp.ActiveWindow.FreezePanes = True
xlApp.Range("A1").Select

Set dbs = Nothing
Set rst = Nothing


'Protect and Save file
'xlBook.Worksheets(1).Protect
xlBook.Save

'done
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing

End Sub

 

[EddyJawed]

Wait! Wait! Wait!

Theres a very simple reason why your 'docmd.transferSpreadsheet' command is throwing this error. You need to check your file path and make sure the extension is ".xls". Sometimes if the correct file extension isn't there and you are trying to export in 'acSpreadsheetTypeExcel9' you'll get the same error. The error can throw your mind off a bit to what is a really simple fix.

Cheers

Eddy

 

[CharlesChuckieCharles]

This works transferring the contents of a table in Access 2007

to a particular sheet in an excel 2007 workbook

DoCmd.TransferSpreadsheet TransferType:=acImport, _
SpreadsheetType:=10, _
TableName:="tbl_export_To_Excel", _
FileName:=ExcelSpreadsheetName, _
HasFieldNames:=True, _
Range:="SheetName!A1:Z1000"

Range can also be a Named range in the workbook!