Failing to Find File
Page 1 of 3 123 LastLast
Results 1 to 10 of 27

Thread: Failing to Find File
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2018
    Posts
    479
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Failing to Find File

    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 by sassriverrat; May 1st, 2019 at 03:59 AM.

  2. #2
    Board Regular bobsan42's Avatar
    Join Date
    Jul 2010
    Location
    Bulgaria, GMT+2 (42.891813,25.313594)
    Posts
    1,281
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Failing to Find File

    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
    "...it's sad that in our blindness we gather thorns for flowers..."
    mostly using:
    windows 7 +10 (64-bit) / excel 2013 +2016 (32-bit) / access 2013 +2016 (32-bit) / some imagination & Google of course
    You don't need to read between the lines - just read them all!

  3. #3
    Board Regular
    Join Date
    Oct 2018
    Posts
    479
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Failing to Find File

    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 by sassriverrat; May 2nd, 2019 at 03:01 AM.

  4. #4
    Board Regular bobsan42's Avatar
    Join Date
    Jul 2010
    Location
    Bulgaria, GMT+2 (42.891813,25.313594)
    Posts
    1,281
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Failing to Find File

    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 by bobsan42; May 2nd, 2019 at 03:42 AM.
    "...it's sad that in our blindness we gather thorns for flowers..."
    mostly using:
    windows 7 +10 (64-bit) / excel 2013 +2016 (32-bit) / access 2013 +2016 (32-bit) / some imagination & Google of course
    You don't need to read between the lines - just read them all!

  5. #5
    Board Regular
    Join Date
    Oct 2018
    Posts
    479
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Failing to Find File

    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?

  6. #6
    Board Regular bobsan42's Avatar
    Join Date
    Jul 2010
    Location
    Bulgaria, GMT+2 (42.891813,25.313594)
    Posts
    1,281
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Failing to Find File

    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-...ml#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.
    "...it's sad that in our blindness we gather thorns for flowers..."
    mostly using:
    windows 7 +10 (64-bit) / excel 2013 +2016 (32-bit) / access 2013 +2016 (32-bit) / some imagination & Google of course
    You don't need to read between the lines - just read them all!

  7. #7
    Board Regular
    Join Date
    Oct 2018
    Posts
    479
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Failing to Find File

    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

  8. #8
    Board Regular bobsan42's Avatar
    Join Date
    Jul 2010
    Location
    Bulgaria, GMT+2 (42.891813,25.313594)
    Posts
    1,281
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Failing to Find File

    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.
    "...it's sad that in our blindness we gather thorns for flowers..."
    mostly using:
    windows 7 +10 (64-bit) / excel 2013 +2016 (32-bit) / access 2013 +2016 (32-bit) / some imagination & Google of course
    You don't need to read between the lines - just read them all!

  9. #9
    Board Regular
    Join Date
    Oct 2018
    Posts
    479
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Failing to Find File

    I'll give it a shot line by line and let ya know. And I'll go check the links. Thanks!

  10. #10
    Board Regular
    Join Date
    Oct 2018
    Posts
    479
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Failing to Find File

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •