Save export with specific filename 1

[Ratman11]

Hi
I'm currently exporting a query result into an excel workbook. However, I now need to export the workbook & save it with a different save name every time (the week number needs to change).

I created a form with an unbound text field for the user to enter the week number in, but i'm really struggling to get the entry carried over into the save name.

Hope that makes sense....any help???!!

 

[HarleyQuinn]

What have you got so far???

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[Ratman11]

The form is called frmRWAReport
The unbound text field is called WeekNumber
There is also a 'Submit' button

The code currently is:

Dim mystring As String
DoCmd.OpenForm "frmRWAReport"
mystring = "frmRWAReport[WeekNumber].Value"

'Export the report.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryRWAReport01", "H:\Service Centre\DataTeam\AccessOutputFiles\RWA Reportwk" & "mystring" & ".xls", True

There is currently no code in the form or button...to be honest i don't know if this code is right, or whether i'm heading in the right direction.

Still trying to get to grips with vba stuff....

 

[earthandfire]

Where is your code?

Assuming the Submit button is on frmRWAReport then in the OnClick event of Submit you could use:

If Nz(WeekNumber,0) = 0 then
msgbox "A valid week number is required"
Else
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryRWAReport01", "H:\Service Centre\DataTeam\AccessOutputFiles\RWA Reportwk" & WeekNumber & ".xls", True
End If

This makes sure that their is a valid week number before exporting.

Hope this helps.

 

[Ratman11]

Thank you!!

That does the job perfectly.

 

[earthandfire]

Glad to have helped.