saving new worksheet

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Saving a new worksheet

Saving a new worksheet

resolvedResolved · Medium Priority · Version 2003

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

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.

 

Training courses

 

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Adding a comment to a formula

1. At the end of the formula, add a + (plus) sign.
2. Type the letter N, and in parentheses, type your comment in quotation marks.

eg.

=CurrentAssets / CurrentLiabilities+ N("The formula returns Current Ratio")

View all Excel hints and tips


Server loaded in 0.07 secs.