MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Need help with a Save as maco

  Asked By: Carol    Date: Jan 07    Category: MS Office    Views: 802

I have a macro to open a TXT file into excel and sort the data, I
would like the file to be saved as .xls file and today's date,
i.e test081106.xls

I tried this macro:
Sub AddSaveAsNewWorkbook()
Dim Wk As Workbook
Set Wk = Workbooks.Add
Application.DisplayAlerts = False
Wk.SaveAs Filename:="C:\MyData\SalesData.xls" Format=
(Date, "yyyymmdd") & ".xls"
End Sub
and I get an error. Can anyone please shed some light on this and
tell me what I'm doing wrong???



1 Answer Found

Answer #1    Answered By: Trae Thompson     Answered On: Jan 07

I think there's a problem with the save  as bit. I'm assuming you want to
save the file  "SalesData 20060811.xls".

I'd get the date  sorted and into a variable before trying the save as

So add
Dim vDate as String
vDate = Format(Date, "yyyymmdd")

and then change the Wk.SaveAs Filename:="C:\MyData\SalesData.xls"
(Date, "yyyymmdd") & ".xls"
Wk.SaveAs Filename:="C:\MyData\SalesData " & vDate & ".xls",

which will give you filename  as above for an Excel file (the xlNormal

Didn't find what you were looking for? Find more on Need help with a Save as maco Or get search suggestion and latest updates.