Naming Exported File to Excel

[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.

 

[John Nurick]

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

 

[Jody]

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

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.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[John Nurick]

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

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.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[Jody]

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

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







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

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.
.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.