Logo 
Search:

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: 727
  

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???

Share: 

 

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
bit.

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

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

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

 
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.




Tagged:      

 
 
 
 

Related Post