Macro Help (run time error)

[dagoof]

Was wondering if anyone could help me out with a Macro someone wrote me to extract address info from some excel invoices I have. Unfortunately I know virtually nothing about Excel programming (apart from some VB programming in high school) so do not know how to sort this run-time error.

The guy who wrote the Macro said it works fine on his machine with Office XP but I am unable to get it to run on either of the Office 2000 machines I have tried (sorry, don't want to to use Office XP, found it terrible ). The macro runs so that it extracts the info in cells E11-E15, then moves onto the next file - unfortunately it comes up with the 'run time error 1004' saying that the 1st file in the sequence cannot be found.

Could anyone shed any light on this? Here is the code:

Sub GetTheDetails()
    
    Linecount = 0

    For Each c In Range("Filenames")
    
      If Trim(c) <> "" Then
    
        Linecount = Linecount + 1
        
        Row = Linecount + 4
        
        t3 = "C" & Trim$(Str$(Row)) & ":" & "G" & Trim$(Str$(Row))
               
        Workbooks.Open c, , ReadOnly
              
        Range("E11:E15").Copy
        
        Workbooks("endsupinhere.xls").Activate
        
        Range(t3).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
        
        
      End If
        
    Next

End Sub

TIA

 

[Anthony47]

Hi dagoof, welcome to the forum.
I would try listing the files with their full path (eg. C:\Documents and setting\Username\My documents\folder\FILE1.xls).

Let's know.

 

[dagoof]

Hi Anthony,

Thanks for the reply. Well, there's about 500 files that run in sequential sequence by invoice number e.g. 1001-companya.xls, 1002-companyb.xls etc.

He'd made up the macro page with a few dummy files (the above 1001-companya.xls etc) and I'd tried running that from a couple of different locations; in a folder in the drive root and a folder on desktop but neither worked. For now I'm just concentrating on getting it working with the dummy files but, when it's operational, he gave me instructions on how to compose a list of the real file names with a DOS (shell) command.

 

[Anthony47]

Excuse me, but I didn't understand if you tested my suggestion or not.

If your concern is how modify so many filenames, well, if they are in the same directory you can use ="C:\path\path\"&A1 (replace A1 with the address of your first filename), then copy the formula down to get the full Path&names of the files; then Paste special, Value in the original list.

You can test this by modifing only the first N files, and see if now the error moves to the unmodified filenames.

Bye,

 

[dagoof]

Ah, sorry I misunderstood Anthony, but I just tried what you suggested and it's worked ray:

Thanks very much for that - I can't believe the guy who wrote the macro hadn't suggested that (as we tried a number of other things).

Whilst I have you here, perhaps you could help me with another quick query - is that any way (or free apps) out there that will compare 2 different spreadsheets and highlight duplicate entries one by one giving you an option to delete? I know there's a few duplicate removers out there but I tried a couple and they either didn't work, pulled stuff out automatically without giving you a chance to 'qualify' it or were impossible to use!

Thanks again