Avoiding 'Save As' dialog box in Excel VBA script

Question

I have cobbled together a VBA script that loops through a list of data, changing the value of a single cell on a summary page. That cell drives a number of formulas. After each iteration, the cell range of interest is saved off as a PDF.

I am looking to avoid having to manually hit enter every time the 'save as' dialog box is generated on each loop. Once I deploy this script, I could be looking at 1k+ iterations.

Sub AlterID()

Dim ws As Worksheet
Dim strPath As String
Dim myFile As Variant
Dim strFile As String

Set ws = Worksheets("Summary Data")

For Each c In Worksheets("Data").Range("A2:A11").Cells   
    Worksheets("Summary Data").Range("B1").Value = c.Value  

    strFile = ws.Range("D3").Value  
    strFile = ThisWorkbook.Path & "\" & strFile

    myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")

    If myFile <> "False" Then
        ws.Range("D3:H9").Select  
        Selection.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=myFile, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
    End If

    Next

End Sub

You will need to describe how you expect locations and filenames to be generated if you're not specifying them manually... — Tim Williams
– Tim Williams, Commented Apr 1, 2016 at 16:13
I guess this is where my lack of knowledge comes into play. The file locations will be static, so I could hard code a file path. The name of the file will just come from the value of a cell (as it does here). — regularGuy
– regularGuy, Commented Apr 1, 2016 at 16:23
Why not Filename:=strFile and get rid of GetSaveAsFilename ? — user4039065
– user4039065, Commented Apr 1, 2016 at 16:29
Just remove the GetSaveAsFilename call and manually construct the filename yourself in code and pass it to the later export line. — ManoDestra
– ManoDestra, Commented Apr 1, 2016 at 16:33

Tim Williams · Accepted Answer · 2016-04-01 16:32:08Z

1

Sub AlterID()

    Dim ws As Worksheet, c As Range
    Dim strFile As String

    Set ws = Worksheets("Summary Data")

    For Each c In Worksheets("Data").Range("A2:A11").Cells

        ws.Range("B1").Value = c.Value  

        strFile = ThisWorkbook.Path & "\" & ws.Range("D3").Value

        ws.Range("D3:H9").ExportAsFixedFormat _
                         Type:=xlTypePDF, _
                         Filename:=strFile, _
                         Quality:=xlQualityStandard, _
                         IncludeDocProperties:=True, _
                         IgnorePrintAreas:=False, _
                         OpenAfterPublish:=False
    Next

End Sub

answered Apr 1, 2016 at 16:32

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mike M · Accepted Answer · 2016-04-01 16:29:09Z

0

Have you tried turning off application alerts?

Application.DisplayAlerts = False 'before your code
Application.DisplayAlerts = True  'after your code

Edit 1

Here is a sub I use to save a file to a PDF

Sub SaveAsPDF()
Dim myValue As Variant
Dim mySheets As Variant

mySheets = Array("Report")
For Each sh In mySheets
    Sheets(sh).PageSetup.Orientation = xlLandscape
Next

uid = InputBox("Enter your UID")
uid = StrConv(uid, vbProperCase)

Application.PrintCommunication = False
    With Sheets("Report").PageSetup
        .FitToPagesWide = 1
        .FitToPagesTall = False
    End With
    Application.PrintCommunication = True

Dim fName As String
fName = "HMB SOX report for week ending " & ActiveSheet.Range("H4").Text
Call selectPage("Report")
With ActiveSheet
        .ExportAsFixedFormat Type:=xlTypePDF, fileName:= _
          "C:\Users\" & uid & "\Desktop\" & fName, :=xlQualityStandard, _
         IncludeDocProperties:=True, IgnorePrintAreas:=False, Publish:=False
End With

End Sub

edited Apr 1, 2016 at 16:29

answered Apr 1, 2016 at 16:23

Mike M

4885 silver badges19 bronze badges

1 Comment

regularGuy Over a year ago

I did try that. The issue, as I think Tim Williams is getting at, is that I used the GetSaveAsFilename function which is generating the box. I would ideally like to not even use that and just hard code the file path and reference a cell for the dynamic file name on each loop.

Collectives™ on Stack Overflow

Avoiding 'Save As' dialog box in Excel VBA script

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related