Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Saving a new worksheet
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Saving a new worksheet
Resolved · Medium Priority · Version 2003
Reply Fri 12 Jun 2009, 10:12Delegate Daren said...
Daren has attended:
Excel VBA Intro Intermediate course
Saving a new worksheet
The programming below creates a new file name from the information in the active workbook
The file name is created
The correct name is displayed in the save as window
But when the save button is selected the save as window disapears, but the workbook is not saved
Can to tell me what I have missed
Sub SaveReport()
Dim VisitNo As String
Dim SiteName As String
Dim SaveName As String
VisitNo = Sheets("DataSheet").Range("H31").Value
SiteName = Sheets("DataSheet").Range("H40").Value
SaveName = SiteName & "-Service Report-" & Date & "-Visit-" & VisitNo
Worksheets("ReportSheet").Activate
Application.GetSaveAsFilename (SaveName)
End Sub
For upcoming training course dates see: Pricing & availability
RE: Saving a new worksheet
Hi Daren,
Thank you for your question.
There were two things I added to make the procedure work:
1. The first problem was the date format as you cannot use the / character when saving a filename.
2. I added a line of code to format the date to ddmmyy.
SaveName = SiteName & "-Service Report-" & Format(Date, "ddmmyy") & "-Visit-" & VisitNo
3. You called the application save as dialogue box to appear but didn't tell excel to save the active workbook.
4. I added ActiveWorkbook.SaveAs Filename:=SaveName
I also added a default location to save the file using the:
ChDir "C:\Users\Simon\Documents"
I hope this answers your question.
Regards
Simon
Tue 23 Jun 2009: Automatically marked as resolved.
|