Importing data from a closed workbook

[andrewb90]

Hi all,

I am trying to import a number of cells (G48:67) from a closed workbook using a macro button. The tricky part is that some of the file name will change every month-ish, and I want to see if I can have the macro adjust for the name change every time. Also the closed worksheet have a different page for every day of the week, and I need the macro to adjust based on the day. Can I have a macro reference information in a cell on the same page to use for importing data?
For example:
The files are labeled
2010P04
2010P05
2010P06

and the sheets are:
Mon
Tue
Wed
Thu
Fri
Sat
Sun

Can anybody help with this?

Thanks,

Andrew

 

[Ruddles]

Yes, you can create a filename using the contents of worksheet cells. If you show us the code where you specify the name of the file you want to import and give us the addresses of the two cells which are going to hold the information, we can show you how to modify the code.

(I would probably use data validation to create a couple of drop-downs in those cells and use those to select the values I wanted to use in the name of the imported file.)

 

[andrewb90]

The file location for the worksheet is C:\Users\RSPM\Desktop\Papa Murphys\WSR\2011\2011P??WSR then the contents in cell b2 on the active sheet will reflect the month (01-12) to fill in the ??. Now in the files are different worksheets that reflect each day of the week. cell b4 will have the day of the week to reference the sheet and then it will pull the data from G45:61 on that particular page and import it into this page (F20:36).

Thanks,

Andrew

 

[Ruddles]

In that case you would set the filename like this:-

dim sfilename as string
sfilename="C:\Users\RSPM\Desktop\Papa Murphys\WSR\2011\2011P" & activesheet.range("b2").value & "WSR" [COLOR=green][B] ' don't forget a file extension![/B][/COLOR]

and you'd create a reference to the correct worksheet like this:-

dim ws as worksheet
set ws=activesheet.range("b4").value

 

[andrewb90]

So I am a bit confused... I am not very experienced with this aspect of Excel. Would I put this in a macro that I am assigning to a button, or am I supposed to be doing something else to make this work?

 

[Ruddles]

You asked how to have the macro adjust for the changing file name and worksheet name by using the values in B2 and B4. The code I provided would get those values and place them in variables called sfilename and ws but you still need to write the code to import the data you want.

Are you saying you don't have any code at the moment?

 

[andrewb90]

Yes, I don't have any code at the moment. That area of excel, I am not very good at yet...Do you have something that would work for me?

 

[Ruddles]

No, sorry, I don't. When you said the tricky part was the changing file name, I assumed you had the rest of the code already written and you just needed that part to finish it off. In fact the changing file name is probably the simplest part of the process.

You could start by doing the process manually and recording a macro, then modifying that macro to suit your exact purposes, but I think this will involve opening the workbook - otherwise how will you address the exact range you're interested in?

 

[MickG]

You could try this for closed Workbook:-
Change File Path in code to suit.
Change "Wkb" extension to suit.
Run Code, Select File name "2010P04" ect from Activesheet, for "Input Box 1".
Select Sheet Name "Mon" ect from ActiveSheet, for "Input Box 2".
Data Returned in column "B".

[COLOR=navy]Sub[/COLOR] MG30May12
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Pth [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] Wkb [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] Sht [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
Wkb = Application.InputBox(prompt:="Please [COLOR=navy]Select[/COLOR] ", Title:="Select File", Type:=2)
    [COLOR=navy]If[/COLOR] Wkb = "" Or Wkb = "False" [COLOR=navy]Then[/COLOR] [COLOR=navy]Exit[/COLOR] [COLOR=navy]Sub[/COLOR]
'Ex:-[COLOR=green][B]Wkb = "2010P04" 'File to Copy from[/B][/COLOR]
Sht = Application.InputBox(prompt:="Please [COLOR=navy]Select[/COLOR] ", Title:="Select sheet", Type:=2)
    [COLOR=navy]If[/COLOR] Sht = "" Or Sht = "False" [COLOR=navy]Then[/COLOR] [COLOR=navy]Exit[/COLOR] [COLOR=navy]Sub[/COLOR]
'Ex:-[COLOR=green][B]Sht = "Wed" ' Sheet to copy from[/B][/COLOR]
Wkb = Wkb & ".xls"
'[COLOR=green][B]Path to copy From[/B][/COLOR]
Pth = "C:\Documents and Settings\test\Desktop\"
Set Rng = Range("G48:G67") '[COLOR=green][B]Range to Copy from[/B][/COLOR]
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    c = c + 1
    [COLOR=navy]With[/COLOR] Cells(c, "B")
         .Formula = "='[B][COLOR=black]" & Pth & "[" & Wkb & "]" & Sht & "'!" & Dn.Address(0, 0) & ""[/COLOR][/B]
         .value = .value
    [COLOR=navy]End[/COLOR] With
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick