Export to Excel with Diff name

  • Thread starter Thread starter GracieLou
  • Start date Start date
G

GracieLou

Hi,

I am using Access 2003.

I am exporting tables to Excell. No problem here.

However, what I want to do is set the name of the Excel file using VB in
Access. I' would like to use NameXLS to name the Excel file I'm exporting to.


Dim NameXLS As String

NameXLS = "rptByPreparer" & cboPreparer(0)

DoCmd.OutputTo acOutputTable, "tblByPreparer", acFormatXLS, _
"G:\3115\namexls.xls"

Application.FollowHyperlink "G:\3115\namexls.xls"

I wnat the name of the Excel file to be "rptByPreparerJSmith". JSmith is
the value of cboPreparer(0). How can I concantenate these two names into one
and have the Excel file have that name?

Thanks to all.
 
I would suggest you investigate using the TransferSpreadsheet method rather
than the OutPutTo method. It is better suited for this and allows better
options. But the real problem is you have namexls inside the quotes. Access
sees that as a string. Any time you use a reference to an Access object or
property, it has to be outside the quotes.

DoCmd.OutputTo acOutputTable, "tblByPreparer", acFormatXLS, _
"G:\3115\namexls.xls"

Should be:
DoCmd.OutputTo acOutputTable, "tblByPreparer", acFormatXLS, _
"G:\3115\" & namexls & ".xls"
 
Klatuu,

Thanks you so much. This is perfect!

Klatuu said:
I would suggest you investigate using the TransferSpreadsheet method rather
than the OutPutTo method. It is better suited for this and allows better
options. But the real problem is you have namexls inside the quotes. Access
sees that as a string. Any time you use a reference to an Access object or
property, it has to be outside the quotes.

DoCmd.OutputTo acOutputTable, "tblByPreparer", acFormatXLS, _
"G:\3115\namexls.xls"

Should be:
DoCmd.OutputTo acOutputTable, "tblByPreparer", acFormatXLS, _
"G:\3115\" & namexls & ".xls"
Click to expand...
 
Back
Top