Access Change Excel File Name

[cbuechner]

Ok, I have a export module in Access. This takes data from at least 10 different queries and paste into excel file. This works good.

As part of this, I use a input box to select which files to select

Dim RFQNUM As String
RFQNUM = InputBox("Enter RFQ Number")

Set rs = CurrentDb.OpenRecordset("SELECT FIELD FROM QUERY WHERE FIELD='" & RFQNUM & "'") '

Then at the end of the code, I close everything.

xlw.Close True   ' close the EXCEL file and save the new data
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing


End Function

I want to use this excel file as the template. I would like to do is rename the excel file before saving it. It would be great if I could rename as the input box value. It ok to be in the same folder, or maybe it would be better to move it.



I am still kind of new to VBA. My understanding is getting better, but I still cant don't know how to write it.

[Welshgasman]

Use the SaveAs option for the excel file.
That will keep the template ready for the next output.

[cbuechner]

Thank you...I am just not sure how to do that part.

This code saves it and closes the file. I guess I can leave it open, but would rather see if I can automatically save the file based on the textbox value.

Use the SaveAs option for the excel file.
That will keep the template ready for the next output.

[Welshgasman]

Thank you...I am just not sure how to do that part.

This code saves it and closes the file. I guess I can leave it open, but would rather see if I can automatically save the file based on the textbox value.

You supply the textbox value as part/all of the filename you are saving as.?
Google Excel Save As VBA and see what that produces.

Here is one of mine (but run in Excel VBA)

Application.StatusBar = "Saving csv file as " & strOutFile
' Save the CSV version
ActiveWorkbook.SaveAs Filename:=strOutFile, FileFormat:=xlCSV, _
    CreateBackup:=False
' And save as an Excel file
' Need to rename Sheet 1 as csv saves makes it the csv name
Application.StatusBar = "Saving Excel file as " & strMainFile
Sheets(1).Name = "Output"
ActiveWorkbook.SaveAs Filename:=strMainFile, FileFormat:=xlNormal

and here I set the value for strOutFile. Note, I needed my file as a CSV file. You would choose an excel format.

strOutFile = Range("K1").Value

You need to modify to suit.

Tip: Google is your friend. Searching for 'Excel Save As VBA' get you plenty of hits to look at.

[cbuechner]

Yes and I am much better at excel, however, I was under the impression the code would be different. If excel code works for this purpose, I will give that a try. Thank you!

You supply the textbox value as part/all of the filename you are saving as.?
Google Excel Save As VBA and see what that produces.

Here is one of mine (but run in Excel VBA)
[code]
Application.StatusBar = "Saving csv file as " & strOutFile
' Save the CSV version
ActiveWorkbook.SaveAs Filename:=strOutFile, FileFormat:=xlCSV, _
CreateBackup:=False
' And save as an Excel file
' Need to rename Sheet 1 as csv saves makes it the csv name
Application.StatusBar = "Saving Excel file as " & strMainFile
Sheets(1).Name = "Output"
ActiveWorkbook.SaveAs Filename:=strMainFile, FileFormat:=xlNormal
[code]
and here I set the value for strOutFile. Note, I needed my file as a CSV file. You would choose an excel format.

strOutFile = Range("K1").Value

You need to modify to suit.

Tip: Google is your friend. Searching for 'Excel Save As VBA' get you plenty of hits to look at.