CODEWITHSUNDEEP
vbaexcel
Jason
Jason

Reputation: 1

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

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

Upvotes: 0

Views: 567

Answers (1)

YowE3K
YowE3K

Reputation: 23974

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

Upvotes: 2

Related Questions