Extract data from open workbook, varying name.

[hutch@edge.net]

Good morning, Gurus.

I run a daily report in which I download data from another source into an excel file, then manipulate the data, (copy and paste), to match existing row headings in my main report. I have created a macro which does this beautifully, with one small problem. Since the name of the imported file changes slightly from day to day, just a number in the file, I have to edit my macro daily to reflect this new file name before running my macro.

Here is the macro, with the part containing the workbook filename highlighted in red. This workbook will always be open when I run my macro, and if necessary, I can assure ONLY this workbook and my main workbook file, (that I copy to), will be the only 2 Excel files open. The name of the file I download always comes in as "ppweb[#].xls", with the # being a single digit number from 1 to 9.

Please Login or Register  to view this content.

Thanks in advance for any help you can offer.

Jerry

[royUK]

All you need to do is ensure hat your import workbook is the open & is activated. Then run the macro. Thisworkbook refers to the workbook containing the code, so this will copy from whichever workbook is active to the workbook that holds the code

Please Login or Register  to view this content.

[hutch@edge.net]

Thanks, Roy. One question: If I open the other workbook, containing the data I want to copy, and then go back to the workbook I want to copy to, whcih contains the macro, then isn't the workbook I want to copy to both the "active" workbook and the "thisworkbook"?

[royUK]

Open the imported workbook, then run the macro from the Tools menu

Tools >- macros >- macro

[hutch@edge.net]

I got it. You can select and run any macro from any open workbook, and the workbook where the macro actually resides is referred to as "thisworkbook".

Simple enough. Thanks.

[hutch@edge.net]

Sorry, Roy, but I screwed something up.

As soon as I run the macro from the active workbook, (the imported one), I get the following error:

Run-time error '438':
Object doesn't support this property or method.

If I select to debug, the first line is highlighted:

Please Login or Register  to view this content.

Any idea what the problem is?

Also, is there a way to run it from my main, (copy to), workbook which contains the macro? That way I could put a button on my main, and just click it to run, rather than having to scroll through the "Tools" menu.

Thanks in advance for all your help.

Jerry

[royUK]

You need to add the sheet name

Please Login or Register  to view this content.

[hutch@edge.net]

How's it going, Roy? Man, you must live on this site. Seems like you're always the first to answer.

I never got this macro working the way I wanted, but I had a better idea. How would I just tell the macro to use the value of a cell as the "copy from" open workbook, and start the macro from there? Then when I see the name of the inported workbook, I just type that name in the cell, and hit the button to run the macro, all from my main workbook.

Sound like a good solution?

[hutch@edge.net]

Greetings, All.

Still looking for a solution to this one. Any ideas?