Code Help

  • Thread starter Thread starter Ronbo
  • Start date Start date
R

Ronbo

I am using Ron de Bruins routine acquiring data from a closed workbook. It
works perfect but the closed workbook does not have a fixed name. The name
of the workbook I need to acquire data from is in sheet3 I1. So the question
is how to I get the following code to use fName to replace test3.xls?

Sub File_In_Local_Folder()
Application.ScreenUpdating = False
On Error Resume Next

fName = Sheets("sheet3").Range("I1").Value

'Call the macro GetRange
GetRange "C:\prc\eom", "test3.xls", "eom information", "A1:bb50", _
Sheets("Sheet1").Range("A1")

On Error GoTo 0
Application.ScreenUpdating = True

End Sub

Thanks
Ronbo
 
What causes the workbook to change names? Is it a fixed criteria? Can that
criteria be used to determine what the current workbook name is? If the name
of the workbook is changed on a strictly arbitrary basis, there is no way to
get it via code, unless it is the only workbook in the folder.
 
The month and year casue the name to change. But I have the name of the
workbook in "sheet3 I1". What I need to do is replace Ron's "test3.xls" (the
file name) to the file name in "sheet3 I1".
 
I assume you mean the the file name is in sheet 3, cell I1 of the active
workbook. If my assumption is correct, then you can use a variable like:

myfName = ThisWorkbook.Sheets("Sheet3").Range("I1")

Then use it like:

GetRange "C:\prc\eom", "myfName", "eom information", "A1:bb50", _
Sheets("Sheet1").Range("A1")
 
Back
Top