VBA Run time error

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi

I'm trying to import 3 files from a folder called "ExcelFilesToImport" that sits within in the Documents path.

But I get a run time error that says "Sorry, we couldn't find Apples.xlsx. It is possible it was moved, renamed or deleted." (see first screenshot attached).

However, the file is definitely an xlsx file and hasn't been moved, renamed or deleted (see second screenshot attached). When you right-click on it and click on Properties, the type is "Microsoft Excel Worksheet (.xlsx)"

Does anyone know what needs to be corrected in the code at the bottom of this message, please?

The files I'm importing are simple:

File 1 is called Apples and has the text 'Apples' in B3 and the number 1 in B4.
File 2 is called Bananas and has the text 'Bananas' in B3, the number 2 in B4, and the number 3 in B5.
File 3 is called Pears and has the text 'Pears' in B3, the number 3 in B4, and the number 4 in B5 and the number 5 in B6.

I'd like to import all of them, so that the data from each file is pasted into one tab in the active file with the macro.

But I'd like to ensure there is a blank row between the data from each file.

Eg if data from File 1 populates cells B3 and B4 in the imported data tab, then data from File 2 would populate cells B5 (row 4 would be blank) and B6.


VBA Code:
Sub ImportInto1Tab()

  Dim FolderPath As String, Filename As String, Sheet As Worksheet, sh As Worksheet

  Dim lr As Long, lc As Long, lr1 As Long

 

  Application.ScreenUpdating = False

  Path = "C:\Users\" & Environ("UserName") & "\Documents\ExcelFilesToImport\"

  'FolderPath = Environ("userprofile") & "\Desktop\Test\"

  'Filename = Dir(FolderPath & "*.xls*")

  Filename = Dir(Path & "*.xls")

  Set sh = Sheets.Add(before:=Sheets(1))

 

  Do While Filename <> ""

    Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True

    For Each Sheet In ActiveWorkbook.Sheets

      lr = Sheet.UsedRange.Rows(Sheet.UsedRange.Rows.Count).Row

      lc = Sheet.UsedRange.Columns(Sheet.UsedRange.Columns.Count).Column

      lr1 = sh.UsedRange.Rows(sh.UsedRange.Rows.Count).Row + 1

      Sheet.Range("A1", Sheet.Cells(lr, lc)).Copy sh.Range("A" & lr1)

    Next Sheet

    Workbooks(Filename).Close

    Filename = Dir()

  Loop

 

  Application.ScreenUpdating = True

End Sub




TIA
 

Attachments

  • screenshot of VBA error.PNG
    screenshot of VBA error.PNG
    47.8 KB · Views: 3
  • screenshot of files in their location.PNG
    screenshot of files in their location.PNG
    23.6 KB · Views: 3

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,854
Office Version
  1. 2007
Platform
  1. Windows
You have the name of the folder in the variable 'Path'
But when reading the file use the variable 'FolderPath'

So it should be like this:
VBA Code:
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,854
Office Version
  1. 2007
Platform
  1. Windows
I recommend you do not use reserved word variables, you can prepend a letter or put a number at the end.
It also uses the Option Explicit statement to varify that all variables are declared.

For example:

VBA Code:
Option Explicit

Sub ImportInto1Tab()
  Dim sPath As String, sFile As String
  Dim wb As Workbook, sh As Worksheet, sh2 As Worksheet
  Dim lr1 As Long

  Application.ScreenUpdating = False

  sPath = "C:\Users\" & Environ("UserName") & "\Documents\ExcelFilesToImport\"
  sFile = Dir(sPath & "*.xls")
  Set sh = Sheets.Add(before:=Sheets(1))

  Do While sFile <> ""
    Set wb = Workbooks.Open(Filename:=sPath & sFile, ReadOnly:=True)
    For Each sh2 In wb.Sheets
      lr1 = sh.UsedRange.Rows(sh.UsedRange.Rows.Count).Row + 1
      sh2.Range("A1", sh2.UsedRange).Copy sh.Range("A" & lr1)
    Next
    wb.Close False
    sFile = Dir()
  Loop
  Application.ScreenUpdating = True
End Sub
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I recommend you do not use reserved word variables, you can prepend a letter or put a number at the end.
It also uses the Option Explicit statement to varify that all variables are declared.

For example:

VBA Code:
Option Explicit

Sub ImportInto1Tab()
  Dim sPath As String, sFile As String
  Dim wb As Workbook, sh As Worksheet, sh2 As Worksheet
  Dim lr1 As Long

  Application.ScreenUpdating = False

  sPath = "C:\Users\" & Environ("UserName") & "\Documents\ExcelFilesToImport\"
  sFile = Dir(sPath & "*.xls")
  Set sh = Sheets.Add(before:=Sheets(1))

  Do While sFile <> ""
    Set wb = Workbooks.Open(Filename:=sPath & sFile, ReadOnly:=True)
    For Each sh2 In wb.Sheets
      lr1 = sh.UsedRange.Rows(sh.UsedRange.Rows.Count).Row + 1
      sh2.Range("A1", sh2.UsedRange).Copy sh.Range("A" & lr1)
    Next
    wb.Close False
    sFile = Dir()
  Loop
  Application.ScreenUpdating = True
End Sub
Amazing!!!

Thank you very much!!!

I hope you enjoy the rest of your weekend!
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

You have the name of the folder in the variable 'Path'
But when reading the file use the variable 'FolderPath'

So it should be like this:
VBA Code:
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
By the way, if you run the original code with the modification you suggested, and you add 'Option Explicit' before the code, the editor says that the 'Path' variable below needs to be defined.

Do you know what it should be defined as, please? Both your suggestions worked!

I'm just curious to know why this won't work IF you add the words 'Option Explicit' at the beginning / what 'Path' should be defined as..... TIA

VBA Code:
Sub ImportInto1Tab()
  Dim FolderPath As String, Filename As String, Sheet As Worksheet, sh As Worksheet
  Dim lr As Long, lc As Long, lr1 As Long
 
  Application.ScreenUpdating = False
  Path = "C:\Users\" & Environ("UserName") & "\Documents\ExcelFilesToImport\"
  'FolderPath = Environ("userprofile") & "\Desktop\Test\"
  'Filename = Dir(FolderPath & "*.xls*")
  Filename = Dir(Path & "*.xls")
  Set sh = Sheets.Add(before:=Sheets(1))
 
  Do While Filename <> ""
    'Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True
    Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
    For Each Sheet In ActiveWorkbook.Sheets
      lr = Sheet.UsedRange.Rows(Sheet.UsedRange.Rows.Count).Row
      lc = Sheet.UsedRange.Columns(Sheet.UsedRange.Columns.Count).Column
      lr1 = sh.UsedRange.Rows(sh.UsedRange.Rows.Count).Row + 1
      Sheet.Range("A1", Sheet.Cells(lr, lc)).Copy sh.Range("A" & lr1)
    Next Sheet
    Workbooks(Filename).Close
    Filename = Dir()
  Loop
 
  Application.ScreenUpdating = True
End Sub
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,396
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Option Explicit forces the user to declare ALL variables correctly, otherwise it will call an error
You need to change the " FoldePath" here to "Path"
Rich (BB code):
Dim FolderPath As String, Filename As String, Sheet As Worksheet, sh As Worksheet
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,854
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

the editor says that the 'Path' variable below needs to be defined.

You must use my code. Path is a reserved word.
In my example I declared it as sPath.
In your code, it is exactly what I warned you.
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Option Explicit forces the user to declare ALL variables correctly, otherwise it will call an error
You need to change the " FoldePath" here to "Path"
Rich (BB code):
Dim FolderPath As String, Filename As String, Sheet As Worksheet, sh As Worksheet
Ok, thank you - I'll have a look at this later - just had an urgent request come up.
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
You must use my code. Path is a reserved word.
In my example I declared it as sPath.
In your code, it is exactly what I warned you.
Ok, thank you - I'll have a look at this later - just had an urgent request come up.
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi @DanteAmor

Do you know if there's way to allow a user to select the path where they want to import the multiple files, using your code above, please?

I know I can use the code below to get to the Downloads folder. But I was wondering if there's a way to combine the code below with your code above, so that a user first selects the folder where they want to import the files from, then clicks a button to execute or import all of them?

Please let me know if you want me to clarify the question further.

Thanks

VBA Code:
Sub OpenDownloadsFolder()




Dim fd As FileDialog
Dim filewaschosen As Boolean
Dim Report As Workbook
Dim iWB As Workbook



Set Report = ActiveWorkbook
Set fd = Application.FileDialog(msoFileDialogOpen)
fd.Filters.Clear
fd.Filters.Add "xlsx files", "*.xlsx"
fd.AllowMultiSelect = False
fd.InitialFileName = Environ("UserProfile") & "\Downloads"
filewaschosen = fd.Show


fd.Execute

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,133,466
Messages
5,658,952
Members
418,478
Latest member
Adam19931

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
Top