Saving an Excel Spreadsheet with a name taken from Access Form 1

[CSADataCruncher]

I currently have a macro that runs behind a command button on my MS Access Form that saves an Excel spreadsheet created from a Query. My problem is, my boss wants the command button to automatically save the file incorporating the "Group" selected on the Access Form as part of the spreadsheet name. I tried adding [Forms]![fTERTrack]![Group] to my Output file name which saved my file as "[Forms]![fTERTrack]![Group]". So, I tried adding the & symbol with an additional word as the file name and putting the [Forms]![fTERTrack]![Group] in parenthesis, but it just added the parens and ampersand to my file name.

Is there a way to save the file with different names depending on the Group name chosen on the form?

Thanks for any help offered,
Peggy

 

[ZmrAbdulla]

Me.Name" for form Name.
if [group] is a list/combobox then "Me.[group].value" or "Me.[group].Column(0)" for the group selected

So it will be something like

"Me.Name & Me.[group].value"

Hope you will be finding a way through this..

________________________________________________________________________
Zameer Abdulla
Visit Me
A person who misses a chance and the monkey who misses its branch can't be saved.

 

[CSADataCruncher]

Sorry... maybe I did something wrong... now it's naming the file Me.Name & Me.[group].value. When I put the quotes around it, Access gave me another error message that said I couldn't save the specified file. One thing I neglected to mention that may be throwing a wrench in the works is that my database resides on a Terminal Server that is accessed by other users within the company.

I'm not real familiar with some of the programming language so, was this supposed to go in a module or in the VB scripting behind the scenes rather than in the "Output File" portion of my macro window?

This is what I'm currently looking at:

In my Macro Design window under Action I have:
SetWarnings
Hourglass
OutputTo
Hourglass
SetWarnings

Then in the boxes down below for the OutputTo action I have:

Object Type: Query
Object Name: qpReceiptTrack-Mgr
Output Format: MicrosoftExcel(*.xls)
Output File: My full path
...\Me.Name & Me.[group].value.xls
Auto Start: Yes

So, am I missing something?

Thank you again, for any help you may be able to provide.
Peggy

 

[ZmrAbdulla]

convert your macro to code..

Private Sub cmdRunMacro_Click()
On Error GoTo cmdRunMacro_Click_Err
[b]Dim x, y As String
x = Me.List3.Column(0)
y = Me.Name
[/b]    DoCmd.SetWarnings False
    DoCmd.Hourglass True
    DoCmd.OutputTo acOutputQuery, "[b]QueryName"[/b], "MicrosoftExcelBiff8(*.xls)", x & y & "[b]MyFileName[/b]", True, "", 0
    DoCmd.Hourglass False

cmdRunMacro_Click_Exit:
    Exit Sub

cmdRunMacro_Click_Err:
    MsgBox Error$
    Resume cmdRunMacro_Click_Exit

End Sub

hope thid helps


________________________________________________________________________
Zameer Abdulla
Visit Me
A person who misses a chance and the monkey who misses its branch can't be saved.

 

[CSADataCruncher]

Thank you so much. Please forgive me for my ignorance... I'm just learning the coding. I just got a Microsoft 2003 VBA book from the store last night and I'll try to breakdown your code so that I understand it.

I did plug it into the VB Editor (Code Builder) behind my command button for running the report, but of course I haven't changed all the items I need to change and the code went to debug format when I clicked my button.

I put my query name into the place that says "Query Name" and I put the name I want added to the Excel spreadsheet name where it says "MyFileName" and I even changed the top line to match the name for my command button, but I wasn't sure what to put for the "Me.Name" and "Me.List3.Column(0)" part (I did take out the quotes around the names I changed as well.

Again, thank you for the code. I'll do more checking this weekend to see if I can figure it out.

BTW... I was REALLY impressed when I clicked your "Visit Me" link and I so appreciate your help.

Thanks,
Peggy

 

[ZmrAbdulla]

You don't have to change "Me.Name" (Current form's name) if you want the form's name to be added to the file name.

"Me.List3.Column(0)" is the listbox and the group selected. So you need to change the listbox name to actual name and column index if necessary.

________________________________________________________________________
Zameer Abdulla
Visit Me
A person who misses a chance and the monkey who misses its branch can't be saved.

 

[CSADataCruncher]

O, I feel so close... this is what is happening. When I click the button, I still go to the debugger... with the error message that label is not defined and it's highlighting my Private Sub/Err lines. Here is the code with the changes I've made... maybe I messed up the coding:

Private Sub MgrRptCmd_Click()
On Error GoTo Err_MgrRptCmd_Click
Dim x, y As String
x = Me.CSAGroup.Column(0)
y = Me.Name
stDocName = "qpReceiptTrack-Mgr"
DoCmd.SetWarnings False
DoCmd.Hourglass True
DoCmd.OutputTo acOutputQuery, stDocName, "MicrosoftExcelBiff8(*.xls)"", x & y & ReceiptTrack, True, "", 0"
DoCmd.Hourglass False

cmdRunMacro_Click_Exit:
Exit Sub

cmdRunMacro_Click_Err:
MsgBox Error$
Resume cmdRunMacro_Click_Exit

End Sub

Once Again, I really am appreciating your knowledge and feel bad that you are working with such an inexperienced user.

Peggy

 

[ZmrAbdulla]

Private Sub [b][COLOR=red]MgrRptCmd[/color][/b]_Click()
On Error GoTo Err_[b][COLOR=red]MgrRptCmd[/color][/b]_Click
Dim x, y As String
x = Me.CSAGroup.Column(0)
y = Me.Name
stDocName = "qpReceiptTrack-Mgr"
DoCmd.SetWarnings False
DoCmd.Hourglass True
DoCmd.OutputTo acOutputQuery, stDocName, "MicrosoftExcelBiff8(*.xls)"", x & y & ReceiptTrack, True, "", 0"
DoCmd.Hourglass False

[b][COLOR=red]MgrRptCmd[/color][/b]_Click_Exit:
Exit Sub

[b][COLOR=red]MgrRptCmd[/color][/b]_Click_Err:
MsgBox Error$
Resume [b][COLOR=red]MgrRptCmd[/color][/b]_Click_Exit

End Sub

All red - bold should be same word

________________________________________________________________________
Zameer Abdulla
Visit Me
A person who misses a chance and the monkey who misses its branch can't be saved.

 

[CSADataCruncher]

It's still not working totally right... it's back to square one (almost) because the command button is now looking at the VB coding rather than the macro, but it's still not giving the file the name we're expecting...

The spreadsheet is being named after the "stDocName" portion (qpReceiptTrack-Mgr). So, I tried taking out the whole stDocName line which almost worked but then it wanted to name the document with the whole string starting with the "MicrosoftExcelBif..." to the end of that line. So, I tried changing "stDocName = Me.CSAGroup.Column(0)" and the file was named "Me.CSAGroup.Column(0)".

For some reason, the code is not recognizing the actual drop-down list like we were originally trying to accomplish.

Any ideas what I'm not doing?
Thank you,
Peggy

 

[ZmrAbdulla]

One more thing I didn't notice yesterday. Sorry.. You didn't declare the document name. It should have " " at the beginning and the end.

Private Sub MgrRptCmd_Click()
On Error GoTo Err_MgrRptCmd_Click
Dim x, y As String
x = Me.CSAGroup.Column(0)
y = Me.Name
stDocName = "qpReceiptTrack-Mgr"
DoCmd.SetWarnings False
DoCmd.Hourglass True
DoCmd.OutputTo acOutputQuery, stDocName, "MicrosoftExcelBiff8(*.xls)"", x & y & [b][COLOR=red]"[/color][/b]ReceiptTrack[b][COLOR=red]"[/color][/b], True, "", 0"
DoCmd.Hourglass False

MgrRptCmd_Click_Exit:
Exit Sub

MgrRptCmd_Click_Err:
MsgBox Error$
Resume MgrRptCmd_Click_Exit

End Sub

Or you need to declare the name as string and use it directly.

Dim x, y , [b][COLOR=red]z [/color][/b]As String
x = Me.CSAGroup.Column(0)
y = Me.Name
[b][COLOR=red]z = "ReceiptTrack"[/color][/b]
stDocName = "qpReceiptTrack-Mgr"
DoCmd.SetWarnings False
DoCmd.Hourglass True
DoCmd.OutputTo acOutputQuery, stDocName, "MicrosoftExcelBiff8(*.xls)"", x & y & [b][COLOR=red]z [/color][/b], True, "", 0"
DoCmd.Hourglass False

hope this helps

________________________________________________________________________
Zameer Abdulla
Visit Me
A person who misses a chance and the monkey who misses its branch can't be saved.

 

[CSADataCruncher]

It seems that the code is ignoring the x, y, & z. It's not picking up the name in the list box. I thought maybe my problem was the fact that the column was set to "not visible" in my query. But when I checked the box to make it visible, it's still not showing.

The code does save my query information into Excel, but it's saving it as Excel 5-7 (I have Excel 2003) and it's saving it by the "stDocName" rather than the x, y, & z. I tried changing the stDocName but then I get an error message that says the JetEngine doesn't recognize the object. Also, I would like Excel to stay open after the document is saved.

I did make the changes you mentioned. When I try the "Receipt Track" in the naming area it didn't like the space between the words, so I deleted the space, but then I got a "compile error".

Here is the code as written now:

Private Sub TER_MgrRptCmd_Click()
On Error GoTo Err_TER_MgrRptCmd_Click

Dim x, y, z As String
x = Me.CSAGroup.Column(0)
y = Me.Name
z = "Receipt Track"

stDocName = "qpReceiptTrack-Mgr"
DoCmd.SetWarnings False
DoCmd.Hourglass True
DoCmd.OutputTo acOutputQuery, stDocName, "MicrosoftExcelBiff8(*.xls)"", x & y & z, True, "", 0"
DoCmd.Hourglass False

Exit_TER_MgrRptCmd_Click:
Exit Sub

Err_TER_MgrRptCmd_Click:
MsgBox Err.Description
Resume Exit_TER_MgrRptCmd_Click

End Sub

Thank you,
Peggy

 

[ZmrAbdulla]

here is a sample to show you how to do.

http://www.geocities.com/zameerabd/NorthWind.zip

(196 kb zip)

It creates an excel file in "C:\" directory.

I have noticed that it doesn't show file icon as excel icon.
Now I need to leave office. I will look into this tomorrow.

________________________________________________________________________
Zameer Abdulla
Visit Me
A person who misses a chance and the monkey who misses its branch can't be saved.

 

[CSADataCruncher]

I'm sorry, the link did not work. It said the page wasn't found.

 

[ZmrAbdulla]

It works now.

http://www.geocities.com/zameerabd/Northwind.zip

________________________________________________________________________
Zameer Abdulla
Visit Me
A person who misses a chance and the monkey who misses its branch can't be saved.

 

[ZmrAbdulla]

I have made some changes in the code to recognize the file as Excel and to keep space between words in the name. See bold areas. Also avoid printing form's name into file name.

Private Sub cmdRunMacro_Click()
    On Error GoTo cmdRunMacro_Click_Err
    Dim x, y, z, stDocName As String
    x = Me.List0.Column(0)
    [b][COLOR=red]y = ".xls"[/color][/b]
    z = "C:\FileName"
    stDocName = "Order Details Extended"
    DoCmd.SetWarnings False
    DoCmd.Hourglass True
    DoCmd.OutputTo acOutputQuery, stDocName, "MicrosoftExcelBiff8(*.xls)", [b][COLOR=red]z & " " & x & " " & y,[/color][/b] False, "", 0
    DoCmd.Hourglass False

cmdRunMacro_Click_Exit:
    Exit Sub

cmdRunMacro_Click_Err:
    MsgBox Error$
    Resume cmdRunMacro_Click_Exit
End Sub

hope this helps

________________________________________________________________________
Zameer Abdulla
Visit Me
A person who misses a chance and the monkey who misses its branch can't be saved.

 

[CSADataCruncher]

Yay! It works... all except for the fact that it doesn't open Excel and, therefore, we have to go by faith that the file was created. It turns out the "z" needed to be the full path of where we wanted the file saved. And it needed to come first in the string - right after the MicrosoftExcelBiff8 portion.

Do you know what bit of code needs to be added to have the file automatically open. Also, I forgot to ask earlier, is there a way that we can also add a date in the file name? I tried adding it as a "w" by typing "w = Date()" (without the parenthesis) then I added w in the string line at top and again in the line below after the MicrosoftExcelBiff8 portion, but the code halted. So, since we have a success to this point, I took that part back out.

I am so excited! The file actually saves with the CSAGroup chosen from the list followed by the file name "ReceiptTrack" (which I substituted for the Me.Name portion).

Thank you so much for all your time and in helping me solve this problem. You have really been a tremendous help!
Peggy

 

[CSADataCruncher]

Hooray!!!! I'm sorry... I hadn't seen your last note when I was posting my note. I added the .xls and now the report opens automatically! Thank you again!

If you do know how I can add the date as well, that would be great, if not that's fine too. We can definitely work with the report at this point and I am ever so greatful for all your help.

Peggy

 

[ZmrAbdulla]

To add Date & Time Use "dt=Now" and for date only "dt=Date".
You can even format it like
[tt]
dt = Format(Date, "yyyy/mmmm/dd")
[/tt]

    Dim x, y, z, stDocName As String
    [b]Dim dt As Date[/b]
    x = Me.List0.Column(0)
    y = ".xls"
    z = "C:\FileName"
    [b]dt = Now[/b]
    stDocName = "Order Details Extended"
    DoCmd.SetWarnings False
    DoCmd.Hourglass True
    DoCmd.OutputTo acOutputQuery, stDocName, "MicrosoftExcelBiff8(*.xls)", z & " " & x & " " & y[b] & " " & dt[/b], False, "", 0
    DoCmd.Hourglass False

________________________________________________________________________
Zameer Abdulla
Visit Me
A person who misses a chance and the monkey who misses its branch can't be saved.

 

[CSADataCruncher]

OK... so, I tried Date(), (Date), Now, Date Format(Date,"m/dd/yy") and they all seem to tell me I have a "Type Mismatch". I exchanged the "dt" for "w" because I have a "DT" field in one of my tables and thought that was throwing off the code. However, at this point, I'm happy the file is saving to Excel with the Group name as part of the file name and opening Excel after the export.

This is the code I currently have (but I'll be removing the date portions until it's working correctly:

Dim x, y, z, stDocName As String
Dim w As Date
x = Me.CSAGroup
y = "ReceiptTrack.xls"
z = "C:\Documents and Settings\peggy_neubert\My Documents\"
w = "Date Format(Date,m/dd/yy)"
stDocName = "qpReceiptTrack-Mgr"
DoCmd.SetWarnings False
DoCmd.Hourglass True
DoCmd.OutputTo acOutputQuery, stDocName, "MicrosoftExcel11(*.xls)", z & x & w & y & ReceiptTrack, True, "", 0
DoCmd.Hourglass False

Again thank you so much for helping me get to this point. I think my boss will be happy with what we've accomplished.

Thank you,
Peggy

 

[CSADataCruncher]

YAY... WooHoo... Party, party, praises to God! The problem is solved!!!!!

It finally dawned on me that the reason we were unable to incorporate the date into the file name is because we can't save slashes ("/") as part of a file name. So, I did further investigating and experimenting and here is the code that works.

Dim x, y, z, stDocName As String
Dim w As Variant
x = Me.CSAGroup
y = "ReceiptTrack.xls"
z = "C:\Documents and Settings\peggy_neubert\My Documents\"
w = CVar(Format(Date, "mm-dd-yy"))

stDocName = "qpReceiptTrack-Mgr"
DoCmd.SetWarnings False
DoCmd.Hourglass True
DoCmd.OutputTo acOutputQuery, stDocName, "MicrosoftExcel11(*.xls)", z & x & w & y & ReceiptTrack, True, "", 0
DoCmd.Hourglass False

See, I changed the date to a Variant (tried Integer but that didn't work). I couldn't be happier!

Thank you again for all your marvelous help. I couldn't have done this without you holding my hand and being so patient with this new coder.

Thank you,
Peggy