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.

"Ronbo" wrote:

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

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".

"JLGWhiz" wrote:

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.

"Ronbo" wrote:

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

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")




"Ronbo" wrote:

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".

"JLGWhiz" wrote:

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.

"Ronbo" wrote:

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