sassriverrat
Well-known Member
- Joined
- Oct 4, 2018
- Messages
- 655
So my code was working perfectly last week (when it ran for the first time. Now it says it can't find the "temp" folder that it created....and I see the folder on teh desktop. It's bugging out at the "Kill (TempFolderOLD) line. Ideas?
Code:
Sub Downloadx()Dim URL As String
Dim tstamp As String
Dim Folder0 As String
Dim Folder1 As String
Dim Folder2 As String
Dim folder3 As String
Dim Namer As String
Dim Date0 As String
Dim Date1 As String
Dim Date2 As String
Dim Date3 As String
Dim Divider As String
Dim LocalFilePath As String
Dim TempFolderOLD As String
Dim OldFinalName 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 LastRow
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("C" & rw) 'Week #
Date1 = .Range("E" & rw) 'Year #
Divider = .Range("D" & rw) '\
Date2 = .Range("G2") 'base week
Date3 = .Range("I3") 'base year
End With
With Sheets("Setup")
Folder0 = .Range("B5") 'temp folder (desktop)
Folder1 = .Range("B7") 'permanent folder (desktop)
Folder2 = .Range("C7") 'permanent folder
folder3 = .Range("C5") 'temp Folder
End With
TempFolderOLD = Environ("Userprofile") & "\" & Folder0 & "\" & folder3 & "\"
tstamp = Format(Now, "mm-dd-yyyy")
TempFileNEW = TempFolderOLD & tstamp & Namer & ".pdf"
LocalFilePath = Environ("Userprofile") & "\" & Folder1 & "\" & Folder2 & "\"
Finalname = Namer & ".pdf"
OldFinalName = LocalFilePath & Finalname
'If these criteria are met, let's begin the download tree
If Date0 <> Date2 And Date1 <> Date3 Then
'Let's assign everything to the temp folder
'Begin by clearing any possible undeleted/corrupted files from my "temp" folder
If MyFSO.FileExists(TempFolderOLD) Then Kill (TempFolderOLD)
'Make a new temp folder
If (Dir(TempFolderOLD, vbDirectory)) = "" 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
If MyFSO.FileExists(OldFinalName) Then
Kill (OldFinalName)
MkDir (LocalFilePath)
End If
'Save temp files to replace old files
'TempFileNEW.SaveAs Filename:=LocalFilePath, FileFormat:=xlTypePDF
MyFSO.CopyFile Source:=TempFileNEW, Destination:=LocalFilePath
'Now delete temp files
Kill (TempFolderOLD)
'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")
'date formating
.Range("C" & rw).HorizontalAlignment = xlRight
.Range("D" & rw).HorizontalAlignment = xlGeneral
.Range("E" & rw).HorizontalAlignment = xlLeft
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"
If MyFSO.FileExists(TempFolderOLD) Then
Kill (TempFolderOLD)
End If
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
Last edited:
