Run Time Error Code 75 on Name [oldfilepath] As [newfilepath] Only when a MsgBox is not output

[timrekg]

Hello All,

I am making vba code that will eventually be stored in the timer property of an access form and needs to run without any user interaction. The vba code opens up all the excel files in a specified folder, does some formatting, and then copies and pastes a certain range into an access table. Then all the files that were in the original folder are moved to an archived folder.

For some reason, I am getting a runtime error 75 path/file access error when I use Name [oldfilepath] As [newfilepath]. For prudence's sake I ran the code again but before the Name As line I had a MsgBox that output my oldfilepath and newfilepath just to make sure they were populating correctly. Sure enough they were and when I ran the sub with the MsgBox - everything worked perfectly and the run time error did not appear. I have tried multiple times where I have removed and added the MsgBox back in and it only works when the MsgBox is in there.

I have absolutely no idea what kind of effect a MsgBox would have but am completely baffled. A snippet from my code is below.

------------------------------------------------------------------

movPath = "C:\Archived Data\Folder\archived"

myFile = Dir(myPath & "\*.*")

Do Until myFile = ""
MsgBox movPath & myFile

Name myPath & myFile As movPath & myFile

myFile = Dir()

Loop
----------------------------------------------------------------



I considered leaving the MsgBox in there but this needs to be auto updating even if no one is at the computer - thus needing someone to click "ok" won't work.

[June7]

I think code execution is suspended until the MsgBox is closed. This may be allowing something occurring in background to complete although not sure what from the posted code. Review https://www.access-programmers.co.uk...d.php?t=167470 and http://access.mvps.org/access/api/api0021.htm

[ssanfu]

It would have been more helpful to see all of the sub, not just a couple of lines. Can't tell "myPath" declaration.

This worked for me:

Option Compare Database
Option Explicit


Public Sub RenameFiles()
    Dim movPath As String
    Dim myFile As String
    Dim myPath As String

    'from path
    myPath = "D:\Forum\Test\"  '<<-- notice trailing backslash (\)

    'to path
    movPath = "c:\Forum\Test\"  '<<-- notice trailing backslash (\)

    'get first file name
    myFile = Dir(myPath & "\")   '<<--  *.* not needed

    Do Until myFile = ""
        Debug.Print myFile & "  " & "From " & myPath & myFile & " To: " & movPath & myFile
        'move files
        Name myPath & myFile As movPath & myFile

        myFile = Dir()   '<<-- get next file name
    Loop
    
    MsgBox "Done"  '<<-- for testing. Delete later
    
End Sub

Change the paths in BLUE to your paths.

[timrekg]

Thanks for you both replying.

Sorry, attached is the entire code as well as a Pause function I tried using to maybe create a delay in the code (similar to the delay that would be caused by a MsgBox).

Option Compare DatabaseOption Explicit


Private Sub Command0_Click()


Dim xlApp As Object
Dim myPath As String
Dim myFile As String
Dim myFilePath As String
Dim myExtension As String


DoCmd.SetWarnings False
Application.Echo False


'create a way to run excel from access
Set xlApp = CreateObject("Excel.Application")
    
'set to look for excel files
myExtension = "*.xls"
    
'set path for folder containing files
myPath = "C:\Users\FileFolder\"


'finds the directory of the file based off path and extension
myFile = Dir(myPath & myExtension)


'collects full file name
myFilePath = myPath & myFile


Do While myFile <> ""
    'opens the first workbook
    xlApp.Workbooks.Open myFilePath
    
    'pauses to make sure the workbook is open
    Pause (2)
    
    'insert edits to worksheet below
    
    With xlApp
        .Range("B12").Select
        .Selection.Copy
        
        .Range("K83:K87").Select
        .ActiveSheet.Paste
        
        .Range("B20").Select
        .Application.CutCopyMode = False
        .Selection.Copy
    
        .Range("L83:L87").Select
        .ActiveSheet.Paste
    
        .Range("B21").Select
        .Application.CutCopyMode = False
        .Selection.Copy
    
        .Range("M83:M87").Select
        .ActiveSheet.Paste
    
        .Range("B71").Select
        .Application.CutCopyMode = False
        .Selection.Copy
        
        .Range("N83:N87").Select
        .ActiveSheet.Paste
        
        .Range("B82:N88").Select
        .Selection.Copy
    End With
    
    'appends the selected range to the given table
    DoCmd.OpenTable ("ACII")
    SendKeys "^{a}"
    DoCmd.RunCommand acCmdPasteAppend
    DoCmd.Close acTable, "ACII", acSaveYes
    
    'pauses again
    Pause (2)
    
    'selects the next file in the directory
    myFile = Dir
Loop


MsgBox "Import Complete"


xlApp.Quit


Dim sKill As String
'Close excel Files
sKill = "TASKKILL /F /IM excel.exe"
Shell sKill, vbHide
    
'path of the folder that will contain the csv files and adds "archived" infront of their name
movPath = "C:\Users\ArchivedFolder\"
    
myFile = Dir(myPath & "\")
Do Until myFile = ""
    Name myPath & myFile As movPath & myFile
    myFile = Dir
Loop


MsgBox "Files Archived"


DoCmd.SetWarnings True
Application.Echo True


MsgBox "Update Complete"


End Sub





Public Function Pause(NumberOfSeconds As Variant)
    On Error GoTo Error_GoTo


    Dim PauseTime As Variant
    Dim Start As Variant
    Dim Elapsed As Variant


    PauseTime = NumberOfSeconds
    Start = Timer
    Elapsed = 0
    Do While Timer < Start + PauseTime
        Elapsed = Elapsed + 1
        If Timer = 0 Then
            ' Crossing midnight
            PauseTime = PauseTime - Elapsed
            Start = 0
            Elapsed = 0
        End If
        DoEvents
    Loop
Exit_GoTo:
    On Error GoTo 0
    Exit Function
Error_GoTo:
    Debug.Print Err.Number, Err.Description, Erl
    GoTo Exit_GoTo
End Function

I'm going to try the suggestions above and report back. Last time I was able to run the code it worked 4 out of 5 times - still not sure why.