Excel VBA run-time error 1004 Application-defined or object-defined error

0 votes

I'm using VBA and writing a macro trying to loop for all excel files in a specific user selected directory and I want to copy its content (which are tables in each excel file), but this error occurs when It's run the code and open the first excel file "Excel VBA run-time error 1004 : Application-defined or object-defined error". I didn't know what is the problem with it.

Here is the code

Sub LoopAllExcelFilesInFolder()
'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them
'SOURCE: www.TheSpreadsheetGuru.com

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog

'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual

'Retrieve Target Folder Path From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With

'In Case of Cancel
NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings

'Target File Extension (must include wildcard "*")
  myExtension = "*.xls*"

'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 wb = Workbooks.Open(Filename:=myPath & myFile)
        
    'Ensure Workbook has opened before moving on to next line of code
      DoEvents

 Range("A1").Select
 Range(Selection, Selection.End(xlToRight)).Select
 Range(Selection, Selection.End(xlDown)).Select
 Selection.Copy
 
 
    'Save and Close Workbook
      wb.Close SaveChanges:=True
      
    'Ensure Workbook has closed before moving on to next line of code
      DoEvents

    'Get next file name
      myFile = Dir
  Loop

'Message Box when tasks are completed
  MsgBox "Task Complete!"

ResetSettings:
  'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

Apr 11, 2022 in Database by Edureka
• 13,690 points
3,985 views

1 answer to this question.

0 votes

Maybe your code is on Sheet 1, and when you switch to Sheet 2, the objects aren't there? If this is the case, simply stating your target worksheet may be beneficial:

Sheets("Sheet1").Range("C21").Select

Because I try to avoid Select as much as possible, I'm not too familiar with how it works:-). Ranges can be defined and manipulated without having to pick them. It's also a good idea to be clear about everything you're referring to. You won't lose track if you switch from one sheet or workbook to another this way. Consider the following:

Option Explicit

Sub CopySheet1_to_PasteSheet2()

    Dim CLastFundRow As Integer
    Dim CFirstBlankRow As Integer
    Dim wksSource As Worksheet, wksDest As Worksheet
    Dim rngStart As Range, rngSource As Range, rngDest As Range

    Set wksSource = ActiveWorkbook.Sheets("Sheet1")
    Set wksDest = ActiveWorkbook.Sheets("Sheet2")

    'Finds last row of content
    CLastFundRow = wksSource.Range("C21").End(xlDown).Row
    'Finds first row without content
    CFirstBlankRow = CLastFundRow + 1

    'Copy Data
    Set rngSource = wksSource.Range("A2:C" & CLastFundRow)

    'Paste Data Values
    Set rngDest = wksDest.Range("A21")
    rngSource.Copy
    rngDest.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    'Bring back to top of sheet for consistancy
    wksDest.Range("A1").Select

End Sub
answered Apr 11, 2022 by gaurav
• 23,260 points

Related Questions In Database

0 votes
0 answers

How to loop in excel without VBA or macros?

Is it possible to iterate (loop) a ...READ MORE

Mar 30, 2022 in Database by Edureka
• 13,690 points
2,408 views
0 votes
1 answer

Excel problem for beginners: #REF! Error When referenced Row is deleted or Added

You'll run into this problem if you ...READ MORE

answered Apr 4, 2022 in Database by gaurav
• 23,260 points
596 views
0 votes
0 answers

Why should I use the DIM statement in VBA or Excel?

So there is a question on what DIM is, but I ...READ MORE

Apr 7, 2022 in Database by Edureka
• 13,690 points
440 views
0 votes
1 answer

Which one is suitable for real time big database- Primary Key or Unique Index?

What is a unique index? A unique index ...READ MORE

answered Oct 12, 2018 in Database by Frankie
• 9,830 points
1,886 views
0 votes
1 answer

Number of elements in a single dimension variant array in excel

You must do UBound - LBound + ...READ MORE

answered Mar 24, 2022 in Database by gaurav
• 23,260 points
791 views
0 votes
1 answer

Pasting an Excel range into an email as a picture

Hold down the ALT + F11 keys ...READ MORE

answered Mar 30, 2022 in Database by gaurav
• 23,260 points
4,773 views
0 votes
1 answer

#NAME? error in Excel for VBA Function

Because you have a module with the ...READ MORE

answered Apr 5, 2022 in Database by gaurav
• 23,260 points
1,470 views
0 votes
1 answer

Excel vba for loop

You must understand how to use loops ...READ MORE

answered Apr 5, 2022 in Database by gaurav
• 23,260 points
709 views
0 votes
1 answer

I have a Run Time Error 91 for an Excel Add In

"Runtime Error 91: Object variable or with ...READ MORE

answered Mar 30, 2022 in Database by gaurav
• 23,260 points
19,976 views
0 votes
1 answer

I have a Run Time Error 91 for an Excel Add In

When there was no unhidden workbook open ...READ MORE

answered Apr 6, 2022 in Database by gaurav
• 23,260 points
978 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP