Problems with Vba to save file & convert to PDF

**si_1970** · 05-17-2016, 12:21 PM

Hi
I am having problems with a couple of macros that i am using to save an Excel workbook and also to create & save PDF version of the same file. All macros work fine on my laptop where the initial workbook was created but i get a Runtime 1004 error for both macros whenever i try and run the macros on any other pc.

The macros is am using are (these are tied to a MasterMacro using the Call function to call each in turn).

Sub Macro1()
'
' Macro1 Macro
' This saves the workbook as an.xlsm workbook with a name the same as cell C20 on the activesheet

    ActiveSheet.Unprotect "password"
    Sheet1.Range("c10").FormulaR1C1 = "=IF(RC[8]=0,"""",NOW())"
    ActiveSheet.Protect "password"
    ActiveWorkbook.SaveAs Filename:="C:\" & Range("c20").Value, FileFormat _
        :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

Sub Macro2()

' This converts the activesheet to a pdf and opens it for viewing

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\" & Range("c20").Value _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True
End Sub

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. Even though you have been a member for 3 years, I have added them for you this time because you only have a few posts. --6StringJazzer

**si_1970** · 05-17-2016, 01:03 PM

Update - The problem appears to be with saving to C:\ because if i change this to D:\ it works fine. Is there a way that i can have a single pop up box or window to specify the save location for both the .xlsm & pdf versions of the file each time?

**si_1970** · 05-17-2016, 03:14 PM

Update 2 - I have got it partly working using an ActiveX button and the below code however there is a problem if i press 'Cancel' to the file browser window when saving. I first get the following message 'A file named 'FALSE.xlsm' already exists in this location. Do you want to replace it?'

If i press No or Cancel I thenget Run-time error '1004' at line 'ThisWorkbook.SaveAs thefilename'

Private Sub CommandButton2_Click()

ActiveSheet.Unprotect "password"
Sheet1.Range("c10").FormulaR1C1 = "=IF(RC[8]=0,"""",NOW())"
ActiveSheet.Protect "password"
ThisFile = Sheets("Header").Range("c20").Value
thefilename = Application.GetSaveAsFilename _
(ThisFile, filefilter:="Excel Files, *.xlsm")
ThisWorkbook.SaveAs thefilename
ThisFile = Sheets("Header").Range("c20").Value
thefilename = Application.GetSaveAsFilename _
(ThisFile, filefilter:="PDF Files, *.pdf")
ThisWorkbook.SaveAs thefilename
Application.Quit
End Sub

**si_1970** · 05-17-2016, 03:32 PM

Update 3 - I think i have cracked it with the following code. It isn't pretty but it appears to work.

Sub sbSaveExcelDialog()

Dim sFileSaveName As Variant
ActiveSheet.Unprotect "password"
Sheet1.Range("c10").FormulaR1C1 = "=IF(RC[8]=0,"""",NOW())"
ActiveSheet.Protect "password"
ThisFile = Sheets("Header").Range("c20").Value
sFileSaveName = Application.GetSaveAsFilename _
(ThisFile, fileFilter:="Excel Files, *.xlsm")
If sFileSaveName <> False Then
ActiveWorkbook.SaveAs sFileSaveName
End If
ThisFile = Sheets("Header").Range("c20").Value
sFileSaveName = Application.GetSaveAsFilename _
(ThisFile, fileFilter:="PDF Files, *.pdf")
If sFileSaveName <> False Then
ActiveWorkbook.SaveAs sFileSaveName
Application.Quit
End If

End Sub

Problems with Vba to save file & convert to PDF

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Problems with Vba to save file & convert to PDF

Re: Problems with Vba to save file & convert to PDF

Re: Problems with Vba to save file & convert to PDF

Re: Problems with Vba to save file & convert to PDF

Thread Information

Users Browsing this Thread

Similar Threads

having problems opening file after VBA save

[SOLVED] convert xlsx to csv and save to file directory

how to convert IXMLDOMNodeList to string or save as XMl file

Excel 2007 file save problems

[SOLVED] Convert, export, or save Word as an image file (jpg, gif, png, etc

Re: Convert, export, or save Word as an image file (jpg, gif, png, etc

[SOLVED] Convert, export, or save Word as an image file (jpg, gif, png, etc

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions