How to extract weekly data and create a report using VBAs

**Roselalala** · 07-09-2013, 07:34 AM

Hi All!

This is my first time using VBAs to run a report and I am learning by doing! I will try and explain my problem as clearly as possible...

I need a macro that will extract data from workbook B and insert it into workbook A, the user will ultimately hit a button on workbook A and activate the macro. I want to run this macro weekly but the name of Workbook B changes weekly.

I am able to create the macro that extracts the data but I am not sure how to make the name of workbook B dynamic. Is it possible for the user to select the workbook (file) they want to run the macro on within workbook A?

How do I do this?? appreciate any help or tips.

Thanks!
Lana

**alansidman** · 07-09-2013, 07:46 AM

Add an input box to your macro requesting the file name of your user.

Please Login or Register  to view this content.

**JOHN H. DAVIS** · 07-09-2013, 07:48 AM

Do you have a naming convention for workbook B? How does the name change.

**Roselalala** · 07-09-2013, 08:24 AM

Thanks Alan, I'll give that a try.

John - The name changes with the date. i.e. Filename_Date

**JOHN H. DAVIS** · 07-09-2013, 08:38 AM

If the date were todays date, something like this may work.

Please Login or Register  to view this content.

**Roselalala** · 07-09-2013, 09:18 AM

Alan - I've tried using your code. Once I have entered the path into the box I run
Windows("inp").Activate
This gives a runtime error and the file doesn't open.
Any ideas why?

**alansidman** · 07-09-2013, 11:14 AM

Shouldn't the line of code read

Please Login or Register  to view this content.

**JOHN H. DAVIS** · 07-09-2013, 11:22 AM

Shouldn't there be a file extension for workbooks?

Workbooks("inp.xlsx").activate?

**alansidman** · 07-09-2013, 11:30 AM

John;
I agree with you, but based upon OP information when he indicated he had path from input box, I assumed (hopefully correctly) that it was in the variable "inp."

Alan