saving a PDF

[sassriverrat]

Good Morning, I'm trying to save a file (that's a pdf) by reading a file from one spot and saving as to another. My code is kicking an "invalid qualifier" on this section of code below and I'm not sure how to better rewrite. Thanks.


section kicked

TempFileNEW.SaveAs Filename:=LocalFilePath, FileFormat:=xlTypePDF

Dim Folder0 As String
Dim TempFolderOLD As String
Dim TempFileNEW As String
With Sheets("Setup")
    Folder0 = .Range("B5") 'temp file
    Folder1 = .Range("B7") 'permanent file
End With
LocalFilePath = Environ("Userprofile") & "\" & Folder1 & "\" & Namer & ".pdf"
tstamp = Format(Now, "mm-dd-yyyy")
TempFolderOLD = Environ("Userprofile") & "\" & Folder0

TempFileNEW = TempFolderOLD & tstamp & "\" & Namer & ".pdf"

TempFileNEW.SaveAs Filename:=LocalFilePath, FileFormat:=xlTypePDF

 

[sadboy309]

I see you want change folder of file. I would you like use FSO (File System Object). It well for cause this.
link: https://trumpexcel.com/vba-filesystemobject/

 

[sassriverrat]

Yes I'd like to change the folder and rename it...


which reading your piece there- begs a quick question- if I turn on scripting runtime on one machine and set up the macro- can it be used effectively on another machine that doesn't have scripting runtime enabled?

 

[sadboy309]

You can try code:

Sub SaveAsPDF()
Dim Namer As String, tstamp  As String
Dim Folder0 As String, Folder1 As String, LocalFilePath As String, FullNameSaveAs As String
Dim MyFSO As FileSystemObject

Set MyFSO = New FileSystemObject


With Sheets("Setup")
    Folder0 = .Range("B7") 'permanent file
    Folder1 = .Range("B5") 'answer file
End With

Namer = "filenamePDF"

LocalFilePath = Environ("Userprofile") & "\" & Folder0  & "\" & Namer & ".pdf"

If MyFSO.FileExists(LocalFilePath) Then
tstamp = Format(Now, "mm-dd-yyyy")

FullNameSaveAs = Environ("Userprofile") & "\" & Folder1 

If not MyFSO.FolderExists(FullNameSaveAs) then
MyFSO.CreateFolder(FullNameSaveAs)
End If

FullNameSaveAs = FullNameSaveAs & "\" & tstamp & " - " & Namer  & ".pdf"

MyFSO.CopyFile Source:=LocalFilePath, Destination:=FullNameSaveAs 

Else

Msgbox ("I don't find file " & chr(10) & LocalFilePath )

End If

End Sub

With Reference to the Scripting FileSystemObject library that Yes:

Dim MyFSO As FileSystemObject
Set MyFSO = New FileSystemObject

May not:

Dim MyFSO As Object
Set MyFSO = CreateObject("Scripting.FileSystemObject")

 

[sassriverrat]

So that article was really cool and very useful- thank you! I tried this- only issue is I'm popping a "control variable already in use" error with "For rw = 4 To sheets" highlighted.

Sub Downloadx()Dim URL As String
Dim tstamp As String
Dim Folder0 As String
Dim Folder1 As String
Dim Namer As String
Dim Date0 As String
Dim Date1 As String
Dim LocalFilePath As String
Dim TempFolderOLD As String
Dim TempFileNEW As String
Dim DownloadStatus As Long
Dim LastRow As Long
Dim Finalname As String
Dim btn As Shape
Dim MyFSO As FileSystemObject
Set MyFSO = New Scripting.FileSystemObject


Dim rw As Long


    ' find last row of data in column B on 'Background'
    LastRow = Sheets("Background").Range("B" & Rows.Count).End(xlUp).Row


    ' loop through rows on 'Background'
    For rw = 4 To Sheets("Background").Range("B" & Rows.Count).End(xlUp).Row
    
        With Sheets("Background")
            Namer = .Range("B" & rw)    'Pub name
            URL = .Range("I" & rw)   'URL to download
            Date0 = .Range("E" & rw)    'Week #
            Date1 = .Range("C" & rw)    'Year #
            For rw = 4 To .Range("B" & Rows.Count).End(xlUp).Row
        End With
        
        With Sheets("Setup")
            Folder0 = .Range("B5")    'temp file
            Folder1 = .Range("B7")    'permanent file
        End With
        
        TempFolderOLD = Environ("Userprofile") & "\" & Folder0
        tstamp = Format(Now, "mm-dd-yyyy")
        TempFileNEW = TempFolderOLD & tstamp & "\" & Namer & ".pdf"
        LocalFilePath = Environ("Userprofile") & "\" & Folder1 & "\"
        Finalname = Namer & ".pdf"
        'If these criteria are met, let's begin the download tree
        If Date1 <> Sheets("Background").Range("G2") And Date0 <> Sheets("Background").Range("I2") Then


            'Let's assign everything to the temp folder
            'Begin by clearing any possible undeleted/corrupted files from my "temp" folder
            If Len(Dir(TempFolderOLD)) <> "" Then Kill (TempFolderOLD)
            'Make a new temp folder
            If Len(Dir(TempFolderOLD)) = "" Then MkDir (TempFolderOLD)
            'Attempt download to the temp folder
            DownloadStatus = URLDownloadToFile(0, URL, TempFileNEW, 0, 0)
            'Check for proper download
            If DownloadStatus = 0 Then
                'Delete the old files
                Kill (LocalFilePath)
                'Save temp files to replace old files
                'TempFileNEW.SaveAs Filename:=LocalFilePath, FileFormat:=xlTypePDF
                MyFSO.CopyFile Source:=TempFileNEW, Destination:=LocalFilePath & Finalname
                'Now delete temp files
                Kill (TempFileNEW)
                'Now update excel sheet to show download passed
                MsgBox "File Downloaded. Check in this path: " & LocalFilePath
                
                With Sheets("Background")
                    .Range("F" & rw) = tstamp
                    .Range("G" & rw) = "SAT"
                    .Range("C" & rw) = Format(Now, "ww", vbWednesday)
                    .Range("E" & rw) = Format(Now, "yy")
                End With
                
                'If download failed, update excel to show- old files should NOT have been deleted yet but the temp file should be deleted
            Else:
                MsgBox "Download File Process Failed"
                Sheets("Background").Range("G" & rw) = "FAIL"
                Kill (TempFolderOLD)
            End If
            'If the original criteria were met and the download was not necessary, say so
        Else


            MsgBox "The most up to date pub has been downloaded"
        End If
        
    Next rw


End Sub

 

[sadboy309]

I don't know, why variable rw not the same type, or other? You try check kind of other.

Dim rw As Long



    ' find last row of data in column B on 'Background'
    LastRow = Sheets("Background").Range("B" & Rows.Count).End(xlUp).Row




    ' loop through rows on 'Background'
    For rw = 4 To LastRow