VBA Run time error

[Mr2017]

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.

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

 

[DanteAmor]

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:

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

 

[DanteAmor]

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:

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]

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:

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]

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:

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

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]

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"

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

 

[DanteAmor]

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]

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"

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]

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]

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

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