date as filename vba

[twl2009]

Im using this line to save a worksheet as a workbook. I want to use a date from the sheet as part of the filename. Vba doesnt like it. Im pretty sure I need to use 'Format..."dd-mm-yy"' around or within 'sh.range("q3")'
But whatever ive tried is giving an error.
Any guidance gratefully received. Thanks


<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff}</style>ActiveWorkbook.SaveAs FileName:="/Users/User/Desktop/" & sh.Name & sh.Range("Q3")

 

[twl2009]

Thanks Avicric,

That saves it as a pdf, but then it tries to save it again as 'workbook 17' or 18 or 19 etc. A message box comes up asking if I want to save or lose data. Its very odd. Here is full code

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff; min-height: 13.0px}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff; min-height: 13.0px}p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993; background-color: #ffffff}span.s1 {color: #011993}span.s2 {color: #000000}</style>Sub saveSheets()


    Application.ScreenUpdating = False


    Dim sh As Worksheet
    For Each sh In ThisWorkbook.Sheets


    If sh.Name <> "Crew Database" And sh.Name <> "Labour Week" And sh.Name <> "Timesheet" Then
            sh.Copy
                
     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:="/Users/User/Desktop/" & sh.Name & " " & Format(Range("Q3"), "dd-mm-yy") _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True


                
            ActiveWorkbook.Close
        End If
    Next


    Application.ScreenUpdating = True


[COLOR=#011993][FONT=Menlo]End[/FONT][/COLOR][FONT=Menlo] [/FONT][COLOR=#011993][FONT=Menlo]Sub[/FONT][/COLOR]/CODE]

 

[Stiuart_W]

It's NOT that odd.

Your line: "ActiveWorkbook.Close" tells the macro to close the workbook - Naturally if any change has been made it asks if you want to save - Just like if you hit the top right cross.

 

[GTO]

Similar to avicric's suggestion; but I would be explicit about where Q3 resides.

Option Explicit
  
Sub example()
Dim sh As Worksheet
Dim Path As String
  
  ' Change sheet and path to suit
  Set sh = Sheet1
  Path = ThisWorkbook.Path & "\"
  
  sh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sh.Name & Space(1) & Format(sh.Range("Q3").Value, "dd-mm-yyyy") & ".pdf", Quality:=xlQualityStandard


End Sub

Hope that helps,

Mark

 

[twl2009]

Ok, I see, but it didnt do this when it was saving as an excel file only now its saving as a pdf.

 

[Stiuart_W]

Ok, I see, but it didnt do this when it was saving as an excel file only now its saving as a pdf.

Because the logic of the "ActiveWorkBook.Close" is this:-

- Has the Workbook changed since last save
- If No then Close
- Else Ask if file should be saved.

Because your code saved the Workbook (as xls format) when you ran that command the Workbook had NOT changed since last save (as you JUST save it) so it closed the Workbook

 

[twl2009]

Is there a way to make it close and not generate the warning message.

 

[Martvg]

do you want the workbook to be saved before closing?

 

[twl2009]

No because code saves it as pdf and original is still open this seems to be just a copy made in the process of exporting as PDF

 

[Stiuart_W]

No because code saves it as pdf and original is still open this seems to be just a copy made in the process of exporting as PDF

Something isn't right. Perhaps you should post ALL your code.

This code you posted

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:="/Users/User/Desktop/" & sh.Name & " " & Format(Range("Q3"), "dd-mm-yy") _
 , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
 :=False, OpenAfterPublish:=True

Will export the activesheet as a pdf and THEN open a copy of that pdf

It does nothing to the Excel Workbook

 

[Martvg]

[COLOR=#333333]Application.ScreenUpdating = False[/COLOR]


[COLOR=#333333]Dim sh As Worksheet[/COLOR]
[COLOR=#333333]For Each sh In ThisWorkbook.Sheets[/COLOR]


[COLOR=#333333]If sh.Name <> "Crew Database" And sh.Name <> "Labour Week" And sh.Name <> "Timesheet" Then[/COLOR]
[COLOR=#333333]sh.Copy[/COLOR]

[COLOR=#333333]ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:="/Users/User/Desktop/" & sh.Name & " " & Format(Range("Q3"), "dd-mm-yy") _[/COLOR]
[COLOR=#333333], Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _[/COLOR]
[COLOR=#333333]:=False, OpenAfterPublish:=False[/COLOR]



[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]Next[/COLOR]


[COLOR=#333333]Application.ScreenUpdating = True[/COLOR]

Can you try this?