[rsernowski]

Hello

I have a macro that retrives data into a template. But I need to have the macro retrieve from the other file no matter what the name is. Ok lets say I have opened spreadsheet named 10971. The macro opens the blank template file and populates correctly. This works good. But I want to have the macro open the template and populate the template from the opened file no matter what the name is! I see on my macro, I see a line like so:
Windows("10971.XLS").Activate
course the macro doesn't work properly unless the one spreadsheet is named 10971! So can I have a variable that retrieves the data from the file no matter the spreadsheet name?
Thanks
Robert

[ScottGem]

You can use a cell reference instead of the hardcoded filename, then populate it with sheet you want to access.

[rsernowski]

Hi Scott,
Are you saying I can put a cell reference in the macro? The original file always is new , as it is an import from another system, but the location of the data I want is always the same. For example, I always need cells :
B1, B4, C4, D4 C8 from the original file

[ScottGem]

I believe you can use:

Windows(Sheet1:A1).Activate

Or something similar.

[JBeaucaire]

There are many attributes you can utilize in your macro to have it determine what the current File Name is.

ThisWorkbook.Name
Result - 10971.xls

ThisWorkbook.FullName
Result - C:\ExcelDocs\TempFiles\10971.xls

You can experiment with this in the part of your code where you want the particular book to be activated.

So maybe:

Windows(ThisWorkbook.Name).Activate

Keep in mind that when working with templates, attributes like this can only be referenced when your new doc based on the template has been saved at least once, thus creating all these attributes.

[ScottGem]

Hi JB,
The way I interpreted this is he is opening a template and wants to pull data into the template from an external file that may change each time. In that case he needs to use a variable where he can enter the filename.

Can you think of another way (maybe an InputBox Like Access has) where he can be prompted for the filename when the macro runs?

[rsernowski]

Hi ScottGem, that is exactly the case. I download the spreadsheet , which always has a unique name. I open it run the macro, it opens a template and populates the template.Actaully thinking to tell the user, to save the template as the unique file name. But first I want my macro o run no matter what the file name is. I have thought about having my macro , just save all the data from the unique file, close the file, paste the data into the template and them cut, copy and paste it from there.What do you think?

[ScottGem]

If the macro works when you hard code the filename, I see no reason why you can't type the actual filename in and reference it that way. At the least, you could just rename the downloaded file.

[JBeaucaire]

I download the spreadsheet , which always has a unique name...
I open it...
... run the macro...
... it opens a template and populates the template.

Based on that order of work process above, I think the variables as I've suggested should do the trick. Used that way, regardless of what the current randomly downloaded filename is, the macro would look it up for itself and use the ThisWorkbook.Name in the appropriate place.

This approach should make even knowing the exact filename irrelevant and an input box also unnecessary.

[rsernowski]

Hi Jb
Look right?

Sub MyMacro()

Dim Thisworkbook.name As Worksheet
Set Thisworkbook.name = ActiveSheet

Windows(ThisWorkbook.Name).Activate
Range("C7").Select
Selection.Copy
Workbooks.Add Template:= _
"C:\Documents and Settings\rsernowski\Application Data\Microsoft\Templates\NBCN Trade Advice.xltx"
Windows("NBCN Trade Advice1").Activate
Range("E4").Select
Windows(ThisWorkbook.Name).Activate
Range("D7").Select
Selection.Copy

[JBeaucaire]

No, ThisWorkbook.Name is a built in function, you don't have to declare it. You could declare a substitute to make your formula look nice, but it's not necessary. This is untested edits to your code:

Sub MyMacro()
Windows(ThisWorkbook.Name).Activate

Range("C7").Copy
    Workbooks.Add Template:= _
        "C:\Documents and Settings\rsernowski\Application Data\Microsoft\Templates\NBCN Trade Advice.xltx"
Windows("NBCN Trade Advice1").Activate
Range("E4").Select
Windows(ThisWorkbook.Name).Activate
Range("D7").Copy

[JBeaucaire]

Hmm, I'm having trouble with that. Perhaps declaring a variable will simplify it:

Sub MyMacro()
Dim MyBook As Worksheet
Set MyBook = ActiveSheet
Windows(MyBook).Activate

    Range("C7").Copy
    Workbooks.Add Template:= _
        "C:\Documents and Settings\rsernowski\Application Data\Microsoft\Templates\NBCN Trade Advice.xltx"

Windows("NBCN Trade Advice1").Activate
Range("E4").Select

Windows(MyBook).Activate
Range("D7").Copy
End Sub

[rsernowski]

Hey Jb

Thanks for your help. The macro is stopping on the line:
Windows(MyBook).Activate
There is only the 3 lines above it! I open up the spreadsheet, in this case it is called 10972 and run the macro - and the macro stops with a dialogue box
Run time error #13
Type mismatch

[JBeaucaire]

Well, let's see if that variable is even being registered properly. Put this line of code above that one and you should get a popup message with the workbook name right before the macro fails in that spot, so at least we know.

MsgBox "The Workbook name is " & MyBook.Name

BTW, I've never used the Windows(MyBook).Activate syntax, but it does not error out on my system, and Intellisense seems to approve it, too.

[rsernowski]

Done
It returns:
The workbook name is sheet1

[rsernowski]

Hi JB,

Maybe I am doing this all wrong. I have 10 cells I require to copy from the "MyBook" spreadsheet. Perhaps I should declare the variable and set it. Close the worksheet , open the template and paste each variable.? If you think it is better just give one sample and I can duplicate it for the other cells. One cell from the open book will be c7 to paste into e4 on the template