Docmd.TransferSpreadsheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to trap the name of the Excel spreadsheet to which the data is being
transferred. This is so I can report the name of the spreadsheet in the On
Error subroutine.

The code is below.

DoCmd.TransferSpreadsheet acExport, 8, "BAPA HT Tam ASCH", NDrive, False, ""

Note: NDrive is the variable that holds the PATH for the file.
 
ACCESS uses the first 31 characters of the "query" or "table" name that is
being exported as the name of the worksheet in the file to which the data
are being exported.
 
Thank you, but it is the Workbook's name that I want to trap not the
worksheet. Because the data that is transferred is variable in the amount of
rows it occupies and there are formulae in the worksheets, it is possible to
get a "Cannot Expand Named Range" error; when the code should advise the
workbook's name in the error message.
 
Thank you, but it is the Workbook's name that I want to trap not the
worksheet.
Click to expand...

I'm not following you. Surely the export routine specifies what you want the
workbook to be called. In which case it's contained in your variable "NDrive".

If you just want the workbook name, use instr or similar on "NDrive" to find
each "\" or "/" in the path. Once you've found the last one, the remainder is
your filename.
 
No the workbook names are not included in the variable "NDrive" as per code
snippet ......

DoCmd.TransferSpreadsheet acExport, 8, "GTeam Trans", NDrive & "GTeam.xls",
False, ""

The GTeam Trans queries' data is transferred as a worksheet into the GTeam
workbook.

The routine exports to about 80 workbooks each month and each of them has 5
TransferSpreadSheets operations (about 400 in total) to export the data into
5 worksheets per workbook.

The Workbook names are hard coded because the new data is transferred to and
overwrites the same workbook/worksheet each month.
 
In your code snippet below, you've hardcoded the workbook name as "GTeam.xls".
If it was the worksheet you were wanting, Ken Snell has advised how to get
this.
DoCmd.TransferSpreadsheet acExport, 8, "GTeam Trans", NDrive & "GTeam.xls",
False, ""
Click to expand...

I'm assuming the value "GTeam.xls" isn't the one you're wanting - but if not
then you'll have to explain exactly what you want because as far as I
understand it, that's the workbook name.
 
In that case you just need something like this....
(note I would leave in the err.description and err.number even if including
your own error narrative, because it's useful... the select case is to allow
you to be able to add other code to handle specific errors that you may wish
to handle differently)

hth. cheers. Jack

Private Sub Button_Click()
On Error GoTo Err_Button_Click

Dim strFileName As String
strFileName = "GTeam.xls"
DoCmd.TransferSpreadsheet acExport, 8, "GTeam Trans", NDrive & strFileName,
False, ""

Exit_Sub:
Exit Sub

Err_Button_Click:
Select Case Err.Number
Case Else
MsgBox "There was a problem exporting file " & strFileName & Chr(13) &
Err.Description, , "Error " & Err.Number
Resume Exit_CmdAdd_Click
End Select

End Sub
 
Back
Top