+ Reply to Thread
Results 1 to 4 of 4

Problems with Vba to save file & convert to PDF

  1. #1
    Registered User
    Join Date
    06-19-2013
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2003
    Posts
    10

    Problems with Vba to save file & convert to PDF

    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).
    Please Login or Register  to view this content.
    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
    Last edited by 6StringJazzer; 05-17-2016 at 01:00 PM.

  2. #2
    Registered User
    Join Date
    06-19-2013
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2003
    Posts
    10

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

    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?

  3. #3
    Registered User
    Join Date
    06-19-2013
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2003
    Posts
    10

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

    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

  4. #4
    Registered User
    Join Date
    06-19-2013
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2003
    Posts
    10

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

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. having problems opening file after VBA save
    By clammastak in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-25-2015, 06:08 PM
  2. [SOLVED] convert xlsx to csv and save to file directory
    By shaz0503 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-27-2014, 07:19 PM
  3. how to convert IXMLDOMNodeList to string or save as XMl file
    By Ranjani in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-18-2009, 05:37 AM
  4. Excel 2007 file save problems
    By JVD in forum Excel General
    Replies: 1
    Last Post: 01-15-2008, 04:48 PM
  5. [SOLVED] Convert, export, or save Word as an image file (jpg, gif, png, etc
    By rjamison in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-13-2005, 08:05 PM
  6. Re: Convert, export, or save Word as an image file (jpg, gif, png, etc
    By rjamison in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-13-2005, 08:05 PM
  7. Convert, export, or save Word as an image file (jpg, gif, png, etc
    By quartz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-20-2005, 12:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1