Failing to Find File

[sassriverrat]

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?

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

 

[bobsan42]

Your email ended up in spam. However I did open the workbook - enabled the code, some things happened i can't spare the time to try and figure out everything but it seems to be working normally on my machine (W10 Ent + Office 365 ProPlus)

I think it is not explicitly a code problem - apart from several debug messages nothing out of the ordinary - wb hides then unhides, opens first time. everything looks OK.

One more thing to try - change the filename slightly ant try to open it again ( or create a copy with a different name and try to run it).

 

[sassriverrat]

settings fixed (I was reverting each one individually as my testing failed- trying to narrow down the problem)....

regarding the hide/unhide- no. All buttons are grayed out except the macros button. Hitting that opens up the macro list window but it's empty. So this literally looks like a copy of excel without a workbook loaded at all. All menus are grayed basically except developer where I can record a macro, etc. Com add-ins are all unselected. If I hit "Check Accessibility" under Review, it says unable to run blah blah blah because no workbook is open to check for accessibility issues.

 

[sassriverrat]

The workbook itself seems to work perfectly, names changed and all.

I am curious- where did you get a debug message? Just when I think I've got one worked out I end up shooting myself in the foot with a new one....

The whole issue I had was just how excel was opening.....just not sure what to do there.

Changing file names resulted in the workbook opening as it should with the different names, so all good there.

 

[bobsan42]

I am glad this helped.

I got an error while the code was trying to save some values in the developer sheet. But i don't even remember the error number.
something like: sheets("Developer").range(...).FormulaR1C1="'No"
This was after I didn't provide the correct (any) password to gain developer access
I didn't follow it to investigate - just thought maybe the sheet was not properly unlocked for some reason since I didn't even know how to use this file.

 

[sassriverrat]

Interesting. I'll look into the error.

Yeah- not sure how to go about excel except maybe a clean install at this point....

 

[bobsan42]

I wouldnt bother reinstalling.
Its a single file name problem. It is not the first time I've heard of it but I still don't know the reason - just some vague idea in the back of my head.
However if the fix is a simple rename - why bother too much
Cheers.

 

[sassriverrat]

sorry I think there's a little confusion there- it's not just this file. Originally it was but it's gotten worse and it's not file association (.xlsm) either....I just tried a .xlsb and a .xls and had the same problem