How to save a file with the location and name as cell values using VBA

Question

Could someone please tell me what I am doing wrong, I am pretty new to VBA and have the following code. I want to open several excel files and save the "Cash" tab as a pdf to a specific folder. The problem I am having is that it tries to save the folder to the "Test" folder and not the folder indicated by the "Cells(r,3)" so I get an error after it saves the first PDF file because they have the same name. Any help would be appreciated!

Sub Cash_PDF_()

r = 2

Do While Cells(r, 5) <> ""

Workbooks.Open FileName:="H:\Investment\Fund Folders\" & Cells(r, 3) & "\" & Cells(r, 5), _
ReadOnly:=True, UpdateLinks:=0

Sheets("Cash").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:="C:\Users\Desktop\Test\" & Cells(r, 3) & "\Cash.pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

ActiveWindow.Close SaveChanges:=False

r = r + 1

Loop

End Sub

YowE3K · Accepted Answer · 2017-04-20 21:18:04Z

2

Cells, when unqualified, refers to the active sheet. You change the active sheet, so it isn't referring to what you intended.

Sub Cash_PDF_()
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim r As Long
    Set ws = ActiveSheet

    r = 2

    Do While ws.Cells(r, 5) <> ""

        Set wb = Workbooks.Open(FileName:="H:\Investment\Fund Folders\" & ws.Cells(r, 3) & "\" & ws.Cells(r, 5), _
                       ReadOnly:=True, UpdateLinks:=0)

        wb.Sheets("Cash").ExportAsFixedFormat _
                        Type:=xlTypePDF, _
                        FileName:="C:\Users\Desktop\Test\" & ws.Cells(r, 3) & "\Cash.pdf", _
                        Quality:=xlQualityStandard, _
                        IncludeDocProperties:=True, _
                        IgnorePrintAreas:=False, _
                        OpenAfterPublish:=True

        wb.Close SaveChanges:=False

        r = r + 1

    Loop

End Sub

edited Apr 20, 2017 at 21:18

answered Apr 20, 2017 at 21:03

YowE3K

24k7 gold badges28 silver badges45 bronze badges

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

1 Comment

YowE3K Over a year ago

@A.S.H - LOL - I wanted to "fastest finger" an answer to get closer to my 10k = so did the minimal fix first and then tidied up. Now I had better tidy up the unqualified Sheets reference - that will annoy me if I don't fix it.

Collectives™ on Stack Overflow

How to save a file with the location and name as cell values using VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related