Click here to Skip to main content
15,903,362 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a run-time error '1004': Application - defined or object-defined error with my codes.

VB
Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")

Dim strWSName As String
strWSName = InputBox("Enter the file path of Excel Files to merge")

If strWSName <> "" Then
    'change folder path of excel files here
    Set dirObj = mergeObj.GetFolder(strWSName)
    
    'header
    Range("A1").Value = "Item"
    Range("B1").Value = "Description"
    Range("C1").Value = "Quality"
    
    Set fileObj = dirObj.Files
    For Each everyObj In fileObj
    Set bookList = Workbooks.Open(everyObj)
    
    Dim rayong As Integer, suzhou As Integer, shenyang As Integer, japan As Integer
    rayong = InStr(1, everyObj, "RAYONG", vbTextCompare)
    suzhou = InStr(1, everyObj, "SZ", vbTextCompare)
    shenyang = InStr(1, everyObj, "Shenyang", vbTextCompare)
    'Note: Still do not know if file has the right format
    japan = InStr(1, everyObj, "JPN", vbTextCompare)
    
    If rayong = 95 Then
    'change "A2" with cell reference of start point for every files here
    'for example "B3:IV" to merge all files start from columns B and row 3
    'If you're files using more than IV column, change it to the latest column
    'Also change "A" column on "A65536" to the same column as start point
    Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
    ThisWorkbook.Worksheets(1).Activate
    'Do not change the following column. It's not the same column as above
    Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
    
    'Notes: Application Error??
    ElseIf suzhou = 100 Then
    Workbooks.Open(everyObj).Activate
    Range("B2:B" & Range("A65536").End(xlUp).Row).Copy
    ThisWorkbook.Worksheets(1).Activate
    Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
    Workbooks.Open(everyObj).Activate
    Range("I2:I" & Range("A65536").End(xlUp).Row).Copy
    ThisWorkbook.Worksheets(1).Activate
    Range("B65536").End(xlUp).Offset(1, 0).PasteSpecial
    Workbooks.Open(everyObj).Activate
    Range("H2:H" & Range("A65536").End(xlUp).Row).Copy
    ThisWorkbook.Worksheets(1).Activate
    Range("C65536").End(xlUp).Offset(1, 0).PasteSpecial
    
    'Notes: Application Error??
    ElseIf shenyang = 95 Then
    ActiveWorkbook.Sheets("WMSInventory").Activate
    Range("A2:A" & Range("A65536").End(xlUp).Row).Copy
    ThisWorkbook.Worksheets(1).Activate
    Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
    ActiveWorkbook.Sheets("WMSInventory").Activate
    Range("B2:B" & Range("A65536").End(xlUp).Row).Copy
    ThisWorkbook.Worksheets(1).Activate
    Range("B65536").End(xlUp).Offset(1, 0).PasteSpecial
    ActiveWorkbook.Sheets("WMSInventory").Activate
    Range("D2:D" & Range("A65536").End(xlUp).Row).Copy
    ThisWorkbook.Worksheets(1).Activate
    Range("D65536").End(xlUp).Offset(1, 0).PasteSpecial
    
    'Notes: Application Error??
    ElseIf japan = 88 Then
    Workbooks.Open(everyObj).Activate
    Range("A2:A" & Range("A65536").End(xlUp).Row).Copy
    ThisWorkbook.Worksheets(1).Activate
    Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
    Workbooks.Open(everyObj).Activate
    Range("O2:O" & Range("A65536").End(xlUp).Row).Copy
    ThisWorkbook.Worksheets(1).Activate
    Range("B65536").End(xlUp).Offset(1, 0).PasteSpecial
    Workbooks.Open(everyObj).Activate
    Range("B2:B" & Range("A65536").End(xlUp).Row).Copy
    ThisWorkbook.Worksheets(1).Activate
    Range("C65536").End(xlUp).Offset(1, 0).PasteSpecial
    
    End If
    
    Application.CutCopyMode = False
    bookList.Close '<---- Error appears here after an if condition.
    Next
Else
    MsgBox "No FilePath Provided! Re-Open this excel to put complete filepath."
End If
End Sub


Please help. :(
Posted
Updated 15-Dec-13 18:49pm
v3
Comments
Ron Beyer 16-Dec-13 0:30am    
Think you could be kind enough to narrow it down to a particular line?
mitchiee1226 16-Dec-13 0:49am    
Narrowed down the question. :)

1 solution

Basically u r getting an error at line "Set bookList = Workbooks.Open(everyObj)" ..
In collection fileObj, you have got all sort of files, even the hidden ones which could be readonly and trying to open such files leads to error, so please update the solution to populate the collection with required type of files only.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900