PDA

View Full Version : Macro to retrieve data from closed workbook



milken
03-26-2006, 06:09 PM
Hi there,

Background: I have a 40mB financial model with 9mB of external links in it (the links are contained on 13 specific "model import" sheets). Those 13 sheets are named with a suffix "FO_MI". My goal is to reduce the size of my workbook by breaking the links in those 13 sheets (valuing them).

In order to be able to retrieve values from the 13 source files in future, I've unsuccessfully tried to write some VBE code to:
1) Bring up a list box with the 13 sheets in it (and ignore the other 160 sheets in my model!). The user then selects any number of the 13 sheets from the listbox.
2) Based upon the selection above, the code will prompt the user for a file name. The filename entered will be that of a model identical to teh current model, except it will contain the 13 import sheets with LIVE links.
3) The macro will open the model and copy across the selected sheets into the first model (pasting values).

Any ideas as to how i can go about the above steps would be much appreciated.

Thanks,
Milken

acw
03-26-2006, 06:52 PM
Milken

1) How about a form which has a listbox and a textbox. The textbox can be used to get the filename and the listbox can contain the sheet names. You don't say, but if the 13 sheets are always the same name then you can use the form_initialize event to load the listbox with a fixed array of names

shtarr = array("sheet1","sheet2")
listbox1.list = shtarr


Alternatively, if the sheet names differ and can be identified by the suffix of fo_mi then something like


For Each ce In ActiveWorkbook.Sheets
If Right(ce.name, 5) = "fo_mi" Then ListBox1.AddItem ce.name
Next ce


Again this would be in the form_initialize event.

2) With the filename from the textbox, you can open the workbook. This does assume that the path / directory is consistent. If not, then you could use the
application.getopenfilename
to bring up the dialog to change the directory / path / filename for the user to select.

3) Depending on how you have initiated the form, you will need to have the source filename available. If you use a button on the sheet from the control toolbar, then you could do something like

thisfilename = Me.Parent.name

to get the filename. Another alternative is in the macro that opens the form you could do


thisworkbook = thisworkbook.name

That way you have a way of returning to the calling workbook.

Once you have the list of sheets, and the names of both the output and source files, you can then loop through the items selected from the listbox, go to the source file / sheet, determine the range, copy, go to the output file / sheet and paste the values.


HTH

Tony

milken
03-26-2006, 09:41 PM
Tony,

Thanks very much for taking the time to reply. I'll try to assimilate your ideas.

Cheers,
Milken

johnske
03-26-2006, 09:46 PM
2) Based upon the selection above, the code will prompt the user for a file name. The filename entered will be that of a model identical to teh current model, except it will contain the 13 import sheets with LIVE links.
3) The macro will open the model and copy across the selected sheets into the first model (pasting values).

Any ideas as to how i can go about the above steps would be much appreciated.

Thanks,
MilkenHi milken, and welcome to VBAX.

I'm curious about the part I've put in bold red above - so the filename mentioned in that sentence won't always be the same file - is that correct? (The next sentence suggests there is only one file...). :dunno

If there is a different file to open depending on the selection, what are the "rules" for which file is to be opened?

Apart from that it all appears pretty straight-forward...

Regards,
John :)

milken
03-26-2006, 09:57 PM
Gidday John,

That's right, the file with the live links will not always be the same. Unfortunately my code doesn't include any rules at present, other than the file must be a .xls file. Thus if the user selects a workbook that doesn't have identical sheet names for the model import, then the code will fall over.

I've shortcutted my original intention of using a listbox (time pressure) and just gone with some code that will update the values for the active model import sheet. This will get me through today :-)


Sub RefreshValues()

' This code will open the source file of your selection, find the _
' sheet with the same name as the active sheet and copy from row 20 _
' downwards into your original worksheet, thus refreshing those values.

' Definitions


Dim CurrentWB, CurrentWS As String
Dim fn As Variant
Dim fnn As String

' Initialise variables and screen flashing

CurrentWB = ActiveWorkbook.Name
CurrentWS = ActiveSheet.Name
Application.ScreenUpdating = False

' Get the source file
fn = Application.GetOpenFilename("Excel-files,*.xls", _
1, "Select One File To Open", , False)
If TypeName(fn) = "Boolean" Then Exit Sub
' the user didn't select a file
Debug.Print "Selected file: " & fn
Workbooks.Open fn
fnn = ActiveWorkbook.Name
Worksheets(CurrentWS).Activate
Rows("20:65536").Copy

' Go back to the orginal workbook and paste values

Windows(CurrentWB).Activate
Worksheets(CurrentWS).Rows("20:65536").PasteSpecial xlPasteValues
Application.CutCopyMode = False
' Close the source workbook
Windows(fnn).Activate
Workbooks(fnn).Close False

Application.ScreenUpdating = True

End Sub

johnske
03-26-2006, 10:10 PM
OK,

When you've drawn up some sort of rules for doing that, get back to us all here if you need some further help.

(I've also taken the liberty of putting your code in VBA tags to make it easier to read)

Regards,
John :)