Macro Programming

[Bunky]

I have a series of functions using Macros that outputs a file in Excel at a
different physical location. I know I can have the path and name of the
output file stagnant or I can have it prompt for a name. What I would like
to do is have the path and part of the name stagnant and then prompt for the
rest of the name. Example:
I need to write to a folder c:\path\path1\path11\name of file but want it to
ask for the name of the file. The person who will be using this macro is
very uneasy with having to type it in. I am uneasy that they will forget to
rename the file to include the date.

Ideas?

Thanks in Advance,

 

[Arvin Meyer [MVP]]

Why don't you use VBA code to automatically, name the file the way you want
it to, and save it?

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MyQry",
"S:\FolderName\MyQuery" & Date() & ".xls", True

 

[Bunky]

Mr. Meyer,

Since I do not know VBA code, I may have a problem. But since you provided
the code, all I have to do is find where to place it.

I thank you!

 

[Arvin Meyer [MVP]]

It may work as a macro. You may have to leaves the quotes out.

If not, what did you use to call the macro? A button's code would look like:

Private Sub cmdMyButton_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"MyQry", "S:\FolderName\MyQuery" & Date() & ".xls", True
End Sub

Just choose [Event Procedure] instead of Macro. Then click on the Build
(...) button change the names to those in your database.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Bunky]

Arvin,

I thank you so much; you're too kind.

Here is what I did. I attached the code you provided in the earlier post,
to the 'On Click' area of the Button. I copied it, modified it to fit my
names, and put in the VB area you get when you click on the expression
builder. Then after I got that working correctly, I put the other macros I
was using, into the code builder and copied that into the code I had just
made. It all worked beautifully.. Thanks so much for your help.

Kent

P.S. the format for the date cannot contain slashes as it causes problems.
Change it to dashes and it is fine.

It may work as a macro. You may have to leaves the quotes out.

If not, what did you use to call the macro? A button's code would look like:

Private Sub cmdMyButton_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"MyQry", "S:\FolderName\MyQuery" & Date() & ".xls", True
End Sub

Just choose [Event Procedure] instead of Macro. Then click on the Build
(...) button change the names to those in your database.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Mr. Meyer,

Since I do not know VBA code, I may have a problem. But since you
provided
the code, all I have to do is find where to place it.

I thank you!

 

[Arvin Meyer [MVP]]

P.S. the format for the date cannot contain slashes as it causes

problems.
Change it to dashes and it is fine.

Sorry, I should have remembered that. AAMOF, when I use a date in a file
name, I usually leave out the separations altogether like:

mmddyy or 062307 for today's date.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin,

I thank you so much; you're too kind.

Here is what I did. I attached the code you provided in the earlier post,
to the 'On Click' area of the Button. I copied it, modified it to fit my
names, and put in the VB area you get when you click on the expression
builder. Then after I got that working correctly, I put the other macros
I
was using, into the code builder and copied that into the code I had just
made. It all worked beautifully.. Thanks so much for your help.

Kent

P.S. the format for the date cannot contain slashes as it causes
problems.
Change it to dashes and it is fine.

It may work as a macro. You may have to leaves the quotes out.

If not, what did you use to call the macro? A button's code would look
like:

Private Sub cmdMyButton_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"MyQry", "S:\FolderName\MyQuery" & Date() & ".xls", True
End Sub

Just choose [Event Procedure] instead of Macro. Then click on the Build
(...) button change the names to those in your database.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Mr. Meyer,

Since I do not know VBA code, I may have a problem. But since you
provided
the code, all I have to do is find where to place it.

I thank you!

:

Why don't you use VBA code to automatically, name the file the way you
want
it to, and save it?

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MyQry",
"S:\FolderName\MyQuery" & Date() & ".xls", True
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have a series of functions using Macros that outputs a file in
Excel
at a
different physical location. I know I can have the path and name of
the
output file stagnant or I can have it prompt for a name. What I
would
like
to do is have the path and part of the name stagnant and then prompt
for
the
rest of the name. Example:
I need to write to a folder c:\path\path1\path11\name of file but
want
it
to
ask for the name of the file. The person who will be using this
macro
is
very uneasy with having to type it in. I am uneasy that they will
forget
to
rename the file to include the date.

Ideas?

Thanks in Advance,