Macro Help (run time error)


New Member
Sep 3, 2006
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 :unsure: ). 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(t3).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
      End If

End Sub

TIA :)

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.
Upvote 0
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.
Upvote 0
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.

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

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 :)
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back