CODEWITHSUNDEEP
excelvba
user3199472
user3199472

Reputation: 41

VBA code from Excel 2003 does not work properly in Excel 2010 / 2013

I am really new to VBA but I have managed to write some code basing on examples mainly from this site.
The piece of code is supposed to copy data from multiple csv files into one xls file (and then to rename the source csv files).
While it works fine in Excel 2003, it does not work so well in Excel 2010 / 2013 (I could not test it in E2007).
It appears to stop after copying data from the first csv file into xls file, so after this line:

Range("B4:AZ" & LR).Copy wbDEST.Sheets("Data").Range("B" & NR)

with Error 1004 "Application-defined or Object-defined error". Debugger highlights the next line, i.e.

NR = wbDEST.Sheets("Data").Range("B" & Rows.Count).End(xlUp).Row + 1

I just cannot see what is wrong with it as the same line is used at the beginning of the code and does not stop there.

I would appreciate your advice.

And here is the whole code:

Option Explicit

Sub ImportData()
Dim fPATH As String, fNAMEcsv As String, fNAMEbak As String
Dim LR As Long, NR As Long
Dim wbSOURCE As Workbook, wbDEST As Workbook

Set wbDEST = Workbooks.Open("C:\Utility\Data.xls")
NR = wbDEST.Sheets("Data").Range("B" & Rows.Count).End(xlUp).Row + 1

fPATH = "C:\Utility\DataFiles\"       'remember the final \ in this string

fNAMEcsv = Dir(fPATH & "*.csv")        'get the first filename in fpath

Do While Len(fNAMEcsv) > 0
    Set wbSOURCE = Workbooks.Open(fPATH & fNAMEcsv, Local:=True)   'open the file
    LR = Range("B" & Rows.Count).End(xlUp).Row  'how many rows of info?

    If LR > 1 Then
        Range("B4:AZ" & LR).Copy wbDEST.Sheets("Data").Range("B" & NR)
        NR = wbDEST.Sheets("Data").Range("B" & Rows.Count).End(xlUp).Row + 1
    End If

    wbSOURCE.Close False   'close data workbook
    fNAMEbak = fNAMEcsv & ".bak"    'rename imported file to .bak
    Name (fPATH & fNAMEcsv) As (fPATH & fNAMEbak)
    fNAMEcsv = Dir         'get the next filename

Loop

MsgBox ("Completed. Check results on PRINTOUT sheet.")

End Sub

Upvotes: 4

Views: 926

Answers (2)

Jean-François Corbett
Jean-François Corbett

Reputation: 38540

The problem could be this:

Here you open a new "source" workbook wbSOURCE:

Set wbSOURCE = Workbooks.Open(fPATH & fNAMEcsv, Local:=True)   'open the file

Maybe it is a workbook in the "new" format (Excel 2007 and later, *.xlsx & Co.). Since you just opened it, it is the active workbook, and therefore the unqualified Rows.Count will return 1048576.

And maybe your destination workbook wbDEST is in the "old" Excel 2003 format (*.xls & Co.). So when you say this:

NR = wbDEST.Sheets("Data").Range("B" & Rows.Count).End(xlUp).Row + 1

you're trying to access wbDEST.Sheets("Data").Range("B" & 1048576) but cell B1048576 doesn't exist in this 2003-format workbook. This will indeed throw an Error 1004 "Application-defined or Object-defined error".

The solution would then be to fully qualify Rows.Count like this:

NR = wbDEST.Sheets("Data").Range("B" & wbDEST.Sheets("Data").Rows.Count).End(xlUp).Row + 1

It's always a good idea to fully qualify everything and not let it depend on the vagaries of Excel.

Upvotes: 1

Doug Glancy
Doug Glancy

Reputation: 27478

Try qualifying your Rows:

NR = wbDEST.Sheets("Data").Range("B" & wbDEST.Sheets("Data").Rows.Count).End(xlUp).Row + 1

Upvotes: 2

Related Questions