Run-Time Error 424 when opening Excel file from a string

Barbatian

New Member
Joined
Sep 12, 2014
Messages
25
Hello,

I have a "Main" file where in "Data base" sheet I store location of a folder containing Excel files I would like to loop/open/copy data and paste back to "Main" file.
When I try to Open the file from a folder, it opens but right after I receive Run-Time Error 424. Could you please help me with finding the reason?
I bold the line where the error occurs.
Rich (BB code):
Sub CollectDataFromEmployeesFiles()
Dim Employee_WB As Workbook
Dim Employee_VT As Worksheet
Dim Main_WB As Workbook
Dim Main_DB As Worksheet
Dim Main_VT As Worksheet
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Set Main_WB = ActiveWorkbook
Set Main_DB = ActiveWorkbook.Sheets("Data Base")
Set Main_VT = ActiveWorkbook.Sheets("Volume Tracker")
'Optimize Macro Speed
    'Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
'Retrieve Target Folder Path From Cell
myPath = Main_DB.Range("P2").Value
'Target File Extension (must include wildcard "*")
  myExtension = "*.xlsx"
'Target Path with Ending Extention
  myFile = Dir(myPath & myExtension)
'Loop through each Excel file in folder
  Do While myFile <> ""
    'Set variable equal to opened workbook
      Set Employee_WB = Workbooks.Open(Filename:=myPath & myFile)
      'Ensure Workbook has opened before moving on to next line of code
      DoEvents
      
    'Take All the Data from Volume Tracker Tab (except first Row)
    Set Employee_VT = ActiveWoorkbooks.Sheets("Volume Tracker")
    Dim LastRowEmployee_VT As Long
    ActiveWorkbooks.Sheets("Volume Tracker").UsedRange
    LastRowEmployee_VT = Employee_VT.UsedRange.Rows(Employee_VT.UsedRange.Rows.Count).Row
    If LastRowEmployee_VT > 1 Then
    ActiveWorkbook.Worksheets("Volume Tracker").Rows("2:" & LastRowEmployee_VT).Copy
    End If
    'Paste Into Main File
    Main_VT.Activate
    Dim LastRowMain_VT As Long
    ActiveWorkbook.Sheets("Volume Tracker").UsedRange
    LastRowMain_VT = Main_VT.UsedRange.Rows(Main_VT.UsedRange.Rows.Count).Row
    
    Main_VT.Rows(LastRowMain_VT + 1).Paste
    
    'Save and Close Workbook
      Employee_WB.Close SaveChanges:=True
      
    'Ensure Workbook has closed before moving on to next line of code
      DoEvents
    'Get next file name
      myFile = Dir
  Loop
ResetSettings:
'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    'Application.ScreenUpdating = True
End Sub
 
Last edited by a moderator:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
There is no such object as ActiveWoorkbooks:

Rich (BB code):
Set Employee_VT = ActiveWoorkbooks.Sheets("Volume Tracker")

You should be using the variable you created Employee_WB instead.
 
Upvote 0
There is no such object as ActiveWoorkbooks:

Rich (BB code):
Set Employee_VT = ActiveWoorkbooks.Sheets("Volume Tracker")

You should be using the variable you created Employee_WB instead.

You are very right, thank you for this spot. Nevertheless The error still occurs (after correction you mentioned).
Why it opens an Excel and then returns error?

I also tried with:
Workbooks.Open Filename:=myPath & myFile

opens the file but also returns same error.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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