Naming Exported File to Excel

  • Thread starter Thread starter Jody
  • Start date Start date
J

Jody

I'm currently using the DoCmd.TransferSpreadsheet to
export data from access to excel. Here's the code:

DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel8, "qryExport", "C:\MachExport.xls",
True

My qryExport is based on an unbound form from which the
user can select the records to export. Can this criteria
be incorporated to change the name of the result .xls file
each time the query is processed? Currently if the user
selects another set of records to export, the previous
file "MachExport.xls" is overwritten. Or, is it possible
to warn the user that the file will be overwritten and
give them an option to change the name?

I'm fairly new to VB so any suggestions would be greatly
appreciated.
 
Hi Jody,

There are several possible approaches. The most generally useful is to
use the code at http://www.mvps.org/access/api/api0001.htm to let the
user choose the folder and filename in the normal way.

Paste the code into a module in your database, and then use something
like this air code in place of your present TransferSpreadsheet
statement:

Dim strFilter As String
Dim strSaveFileName as string

'Display dialog
strFilter = ahtAddFilterItem(strFilter, _
"Excel Files (*.XLS)", "*.XLS")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)

If Len(strSaveFileName) > 0 Then 'User entered a filename
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel8, "qryExport", _
strSaveFileName
Else 'User pressed Cancel
'Do nothing

End If
 
Thanks John for the reply.

I'm reviewing the document you refer to. However, since
I'm VB deficient, I'm very confused as to what code I need
to extract to insert into a module in my database. There
seems to be quite a bit of the code that would not be
applicable to my particular issue. Please forgive my
ignorance, I'm still learning about modules.

-----Original Message-----
Hi Jody,

There are several possible approaches. The most generally useful is to
use the code at
Click to expand...
http://www.mvps.org/access/api/api0001.htm to let the
user choose the folder and filename in the normal way.

Paste the code into a module in your database, and then use something
like this air code in place of your present TransferSpreadsheet
statement:

Dim strFilter As String
Dim strSaveFileName as string

'Display dialog
strFilter = ahtAddFilterItem(strFilter, _
"Excel Files (*.XLS)", "*.XLS")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)

If Len(strSaveFileName) > 0 Then 'User entered a filename
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel8, "qryExport", _
strSaveFileName
Else 'User pressed Cancel
'Do nothing

End If







I'm currently using the DoCmd.TransferSpreadsheet to
export data from access to excel. Here's the code:

DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel8, "qryExport", "C:\MachExport.xls" ,
True

My qryExport is based on an unbound form from which the
user can select the records to export. Can this criteria
be incorporated to change the name of the result .xls file
each time the query is processed? Currently if the user
selects another set of records to export, the previous
file "MachExport.xls" is overwritten. Or, is it possible
to warn the user that the file will be overwritten and
give them an option to change the name?

I'm fairly new to VB so any suggestions would be greatly
appreciated.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Jody,

Copy everything from
'***************Code Start*********
to
'***************Code End***********

Don't worry that not all of it seems necessary; the code was written and
tested as a complete module. There's precious little to be gained from
trying to omit bits of it, and a good chance that you'll stop it
working.


Thanks John for the reply.

I'm reviewing the document you refer to. However, since
I'm VB deficient, I'm very confused as to what code I need
to extract to insert into a module in my database. There
seems to be quite a bit of the code that would not be
applicable to my particular issue. Please forgive my
ignorance, I'm still learning about modules.

-----Original Message-----
Hi Jody,

There are several possible approaches. The most generally useful is to
use the code at
Click to expand...
http://www.mvps.org/access/api/api0001.htm to let the
user choose the folder and filename in the normal way.

Paste the code into a module in your database, and then use something
like this air code in place of your present TransferSpreadsheet
statement:

Dim strFilter As String
Dim strSaveFileName as string

'Display dialog
strFilter = ahtAddFilterItem(strFilter, _
"Excel Files (*.XLS)", "*.XLS")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)

If Len(strSaveFileName) > 0 Then 'User entered a filename
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel8, "qryExport", _
strSaveFileName
Else 'User pressed Cancel
'Do nothing

End If







I'm currently using the DoCmd.TransferSpreadsheet to
export data from access to excel. Here's the code:

DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel8, "qryExport", "C:\MachExport.xls" ,
True

My qryExport is based on an unbound form from which the
user can select the records to export. Can this criteria
be incorporated to change the name of the result .xls file
each time the query is processed? Currently if the user
selects another set of records to export, the previous
file "MachExport.xls" is overwritten. Or, is it possible
to warn the user that the file will be overwritten and
give them an option to change the name?

I'm fairly new to VB so any suggestions would be greatly
appreciated.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
It works! Thanks, John, for all your help.
-----Original Message-----
Jody,

Copy everything from
'***************Code Start*********
to
'***************Code End***********

Don't worry that not all of it seems necessary; the code was written and
tested as a complete module. There's precious little to be gained from
trying to omit bits of it, and a good chance that you'll stop it
working.


Thanks John for the reply.

I'm reviewing the document you refer to. However, since
I'm VB deficient, I'm very confused as to what code I need
to extract to insert into a module in my database. There
seems to be quite a bit of the code that would not be
applicable to my particular issue. Please forgive my
ignorance, I'm still learning about modules.

-----Original Message-----
Hi Jody,

There are several possible approaches. The most
Click to expand...
generally
useful is to
use the code at
Click to expand...
http://www.mvps.org/access/api/api0001.htm to let the
user choose the folder and filename in the normal way.

Paste the code into a module in your database, and then use something
like this air code in place of your present TransferSpreadsheet
statement:

Dim strFilter As String
Dim strSaveFileName as string

'Display dialog
strFilter = ahtAddFilterItem(strFilter, _
"Excel Files (*.XLS)", "*.XLS")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)

If Len(strSaveFileName) > 0 Then 'User entered a filename
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel8, "qryExport", _
strSaveFileName
Else 'User pressed Cancel
'Do nothing

End If







On Wed, 15 Sep 2004 07:21:56 -0700, "Jody"

I'm currently using the DoCmd.TransferSpreadsheet to
export data from access to excel. Here's the code:

DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel8, "qryExport", "C:\MachExport.xl
Click to expand...
s"
,
True

My qryExport is based on an unbound form from which the
user can select the records to export. Can this criteria
be incorporated to change the name of the result .xls file
each time the query is processed? Currently if the user
selects another set of records to export, the previous
file "MachExport.xls" is overwritten. Or, is it possible
to warn the user that the file will be overwritten and
give them an option to change the name?

I'm fairly new to VB so any suggestions would be greatly
appreciated.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Back
Top