MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Saving files to specific location using VBA

  Asked By: Fahimah    Date: Sep 03    Category: MS Office    Views: 9463

I need generic code to add a new workbook & save it using a filename
format & for some reason the code below is not working.

What I am trying to do ultimately is, from a master annual workbook,
add a new workbook to a subfolder named after the relevant month (03
for March etc) & save to that folder(03) as filename "DC" & mm (march
master FN would be DC03). This file is known as the monthly master as
there could be up to 10 CSV files per month to be consolidated into
the master monthly.

I then do other things with these montly files to consolidate into
annual file but for the moment I have this key problem & this is what
I have (* I have experimented, search the net * have several books on
VBA but cannot get syntax or command right)

Set NewBook = Workbooks.Add
With NewBook
.Title = "DC08" & mnth
.Subject = "DC"
.SaveAs Filename:="C:\Documents and Settings\New
Owner\My Documents\AT\BNZ TransactionsHist\08DC\" _
& mnth & "\" & "DC08" & mnth
End With

It won't save the file to the folder I am trying to nominate by user
input called "mnth". So I want create a master file for the month &
save that file in the monthly folder.

My code works as separate operations but there is still a bit of
automation I can do as one coding operation.

I also need this as I have 2 other operations I do monthly that will
use this code.



5 Answers Found

Answer #1    Answered By: Laura Rodriguez     Answered On: Sep 03

Have you created the folder? I think you can only "save as" to an existing
What error do you get?

Answer #2    Answered By: Spiru Kelly     Answered On: Sep 03

This has now been resolved thanks David.

Yes the folder  had been created but with a bit  more searching other
examples I found that I needed ChDir command  with FilePath string

However I do now have another minor problem  but I will post as new
query anyway but as it might take a few minutes to get through here

I want to delete unwanted sheets that have nothing in them & I can do
it like this

'For Each myWorksheet In basebook.Worksheets
'If myWorksheet.Name <> sN Then
' myWorksheet.Delete
'End If
'Next myWorksheet
However I get the message "All data will be lost. Do I want to

As I have two sheets that I need to delete I want to add  the code  to
automatically delete these 2 sheets.

What is the code?

Answer #3    Answered By: Jenny Lopez     Answered On: Sep 03

You have a folder  name in your save  statement of 08DC which doesn't match
what you were saying in the description. I think you have your backslash in
the wrong place. (In fact, you also have a second DC08 and a second use of
mnth. Much more stuff than you have in the description.)

As a matter of principal, do not construct something like a file  name
directly in the parameter of a call to a method. It makes things  much
harder to debug.

Create a variable and put the constructed file name in there, then check it
(e.g. using MsgBox or just view it in the VBE), and then use this file name
variable in the save call instead of the expression.

If you'd done it that way, you would almost certainly have spotted the
problem immediately, yourself.

Answer #4    Answered By: Aiko Suzuki     Answered On: Sep 03

I did realise that I was making it difficult after searching the net
& looking at other examples.

My current difficulties have been resolved.

Answer #5    Answered By: Ellen Simpson     Answered On: Sep 03

Not sure why you'd need a ChDir command. I have Excel code  that saves to
many and varied directories without needing ChDir.

To stop the messages when deleting the sheets you'll need to turn them off

Application.DisplayAlerts = False

then delete the sheets, then

Application.DisplayAlerts = True

to get the alerts back on again.

Didn't find what you were looking for? Find more on Saving files to specific location using VBA Or get search suggestion and latest updates.