Failing to Find File

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:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
since you already created a FSO use its methods instead of Kill and MkDir e.g. FSO.DeleteFolder(FileSpec, Force (True/False)) and FSO.CreateFolder(Path)
If nothing else, at least for consistency - if FSO tells you the folder exists then it must be able to find and delete it. And it has the advantage to treat consecutive \ as one.
check this for complete reference: https://ss64.com/vb/filesystemobject.html
And when checking for folders use FSO.FolderExists instead of .FileExists
 
Upvote 0
so FSO is not capable of creating multiple folders at once, within each other, right? As in, if I wanted a folder called "Hello" inside of a folder called "Greetings" and neither folder existed (and I wanted "Greetings" on teh desktop), I can't do the following, right? I have to create one folder and then the other?

Code:
MyFSO.CreateFolder(Environ("Userprofile") & "\" & Desktop & "\" & "Greetings & "\" & "Hello"

vs. 

MyFSO.CreateFolder(Environ("Userprofile") & "\" & Desktop & "\" & "Greetings   'create 1
MyFSO.CreateFolder(Environ("Userprofile") & "\" & Desktop & "\" & "Greetings & "\" & "Hello" 'then create 2

Update- I know I can do it the second way but didn't know if the first way was possible and I had it wrong or there was a way to condense the code of the second way.

Also- is there any advantage of using FSO.CreateFolder (or other FSO functions) vs. Like MkDir or Len(Dir())?
 
Last edited:
Upvote 0
AFAIK you are correct - folders must be created one level at a time. The same goes for MkDir.
Advantages - I don't know if they can be called this. There are small differences in the methods and their parameters. For me it is more a matter of consistency - if I need to do a more complicated task and have to create FSO, then use FSO rather than creating confusion.
Efficiency can be checked on a large number of files / folders, but I wouldn't expect any major differences.
An example of a difference is:
you can create a folder like this:
Code:
MkDir Environ("Userprofile") & "\" & Desktop & "\" & "Greetings & "\"
then you change current folder:
Code:
Chdir Environ("Userprofile") & "\" & Desktop & "\" & "Greetings & "\"
then you can create many folders in Greetings just like this:
Code:
MkDir "Hello1"
MkDir "Hello2"
...

The biggest difference is that FileSystem commands are internal to VBA (built-in), while FSO is dependent on the external scripting library.
 
Last edited:
Upvote 0
but the external scripting library doesn't have to be enabled, assuming that I've got the FSO dimmed correctly, agreed?



Secondary note- when Excel 2010- open a workbook no problem- I do get the security warning about asking to enable macros but enable and it's great.
excel 2016- attempt to open the same workbook (also from the desktop) and double clicking opens up excel with nothing loaded, then double clicking the desktop workbook again opens the workbook I want (as it should have the first time) and still leaves a second instance of excel open with nothing loaded still in that window. Ideas?
 
Upvote 0
When you do late-binding you don't have to create an explicit reference to the external library.
However if the macro has to run on another system/computer the library must be present and registered on this compute also. If it is missing - the macro won't run.

Secondary note:
There can be many reasons for having a blank application window. Have you checked if the workbook is opened? Any chance that the workbook window is hidden?
Assuming that you get no warning upon opening it a second time I would guess it remains closed, but still ...

Anything specific about workbook name and file type?
Any code that runs on Workbook opening?
You can see here if anything can help: https://www.mrexcel.com/forum/excel...e-excel-2013-pro-post5250279.html#post5250279
Also check if the file is not Blocked if been received by email or something: File properties/General tab/ if you see an UNBLOCK button there - click it.
 
Upvote 0
So as long as it's dimmed correctly, it can run on a computer that doesn't have the library registered...right? Or did I misread that?


Secondary note:
Not open. I can restart the computer, go to open, and it does the same thing. I am transferring this between the old machine (Excel 2010, windows 7) and the new machine (Excel 2016, 10PRO) via flashdrive.

No warning when I open it the second time. sometimes when I open the second time it will give me the "active macros disabled" message along the top of the workbook window but that's expected/usual. There still remains an unloaded, opened first excel window that I can't explain.

nothing blocked. no funky attributes. just a .xlsm file

code that runs during startup below

Code:
Private Sub Workbook_Open()


'This will show the Userform 1 when starting and hide the excel sheet in the background
'Also checks to see if program has ever been run, and if not, loads program for the first time
Application.Visible = False
Dim s As String
Dim edate As String
Dim namer As String
Dim d As String
Dim ExpirationDate As String


ExpirationDate = edate
d = Sheets("Developer").Range("B39")          'registration key
edate = Sheets("Developer").Range("E37")      'expiration date
namer = Sheets("Notes").Range("N4")           'just a name


s = GetSetting("DemoTest", "Registration", "Username")
If s = "" Then
        Sheets("Developer").Unprotect Password:=Worksheets("Developer").Range("B15:E15").Value
        Sheets("Developer").Range("B34:F34").ClearContents
        's = InputBox("Welcome to the " & namer & " Voyage Reporting System." & vbCrLf & "Please input the appropriate name to initialize the system for the first time." & vbCrLf & vbCrLf & "Note: this information can be modified later by clicking on the [Developer] button.", namer, "Bridge")
        'UserForm17.Show
        s = cInputBox()
        MsgBox s
        
        If s <> "" Then
            's = cInputBox()
            Sheets("Developer").Range("B34") = s
            SaveSetting "DemoTest", "Registration", "Username", s
            Sheets("Notes").Visible = xlSheetVisible
            Sheets("Notes").Select
            Sheets("Developer").Unprotect Password:=Worksheets("Developer").Range("B15:E15").Value
            Sheets("Developer").Range("C36") = Date
            'If s <> "" Then MsgBox "Welcome to the " & name & " Voyage Reporting System." & vbCrLf & "Please input the appropriate data to initialize the system for the first time." & vbCrLf & vbCrLf & "Note: this information can be modified later by clicking on the [Developer] button.", vbOKOnly, name
            Application.Visible = True
        End If


Else:
    'If ExpirationDate > edate Then
        If ExpirationDate < Date Then
            If ActiveWorkbook.name = "Master Voyage Report.xlsm" Then
                UserForm1.Show
            ElseIf ActiveWorkbook.name = "Current Voyage Report.xlsm" Then
                UserForm2.Show
            Else: UserForm3.Show
            End If
        Else: d = Application.InputBox("Your workbook date has expired. Please enter the registration key to renew your license.", namer)
            If d = CStr(Worksheets("Developer").Range("B39").Value) Then
                    Sheets("Developer").Range("C36") = Date
                    MsgBox "Welcome Back " & s, vbOKOnly, namer
            End If
        End If
    'End If
        
        
End If
Application.Visible = True   'inserted just to check workbook
'Protects/Hides sheets on startup
Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets
        If sh.name = "Notes" Then
            sh.Protect Password:=Worksheets("Developer").Range("B17:E17").Value, UserInterfaceOnly:=True
            End If
        If sh.name = "Ports" Then
            sh.Protect Password:=Worksheets("Developer").Range("B19:E19").Value, UserInterfaceOnly:=True
            End If
        If sh.name = "Developer" Then
            sh.Protect Password:=Worksheets("Developer").Range("B15:E15").Value, UserInterfaceOnly:=True
            End If
    Next sh
    
End Sub
 
Upvote 0
The external library does not have to be referenced in your project, but it must be installed and registered on the client's computer - VBA must be able to find it and access it for the code to work.

Your code looks OK. But obviously something is wrong - probably excel or system settings - check the post I linked above - see if any of the links will help.
Also try to open the workbook with the WorkbookOpen code disabled - see if there is any change. If yes try commenting section by section to check for behavior change.
 
Upvote 0
So I had been through most of those and the solution was something I had not come across (namely for people reading this forum that might suffer from the same problem!).

So here's what I did:

1. Save and close all active workbooks and anything else office related.
2. Go to %appData%\Microsoft\Excel\XLSTART\ (for those that might have issues with this, open a new folder, type %appData% in the above bar and navigate from there)
3. Once in the XLSTART folder, I deleted the two of the three files present, leaving just the XLSTART folder in place. I also checked inside the folder and made sure it was empty.
4. Close all windows, then reopen the problem-child workbook.
5. PROBLEM SOLVED!


Where did this come from? In my case, I noticed one of the two files/folders I deleted was a copy of the "corrupted workbook." the workbook itself wasn't corrupted, but one of the save macros couldn't find the address to which it was to save and somehow did manage to save here. My guess is that is what caused the issue.

Hope this helps and thank you to @bobsan42
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top