Copy Data from Closed Workbook-WrkBk Name Varies

[MSingh]

Hi,

I am trying to import data from closed workbook "A.xlsm" Sheet1 Range("A1:I220") using Ron De Bruin's GetRange function.
This works if the "A.xlsm" being accessed is written into the code. How can i adapt the code for a file whose name changes according to user input.

In Sub GetAmounts_1 I have tried:

Dim fn as Variant
fn=Sheet1.Range("AA1") 'file name copied & pasted to "AA1"
then replaced "A.xlsm" with fn
but this gave a type mismatch error.

Sub GetRange(FilePath As String, FileName As String, SheetName As String, _
SourceRange As String, destRange As Range)

'Ron De Bruin

Dim Start

'Go to the destination range
Application.GoTo destRange

'Resize the DestRange to the same size as the SourceRange
Set destRange = destRange.Resize(Range(SourceRange).Rows.Count, _
Range(SourceRange).Columns.Count)

'Add formula links to the closed file
With destRange
.FormulaArray = "='" & FilePath & "/[" & FileName & "]" & SheetName _
& "'!" & SourceRange

'Wait
Start = Timer
Do While Timer < Start + 2
DoEvents
Loop

'Make values from the formulas
.Copy
.PasteSpecial xlPasteValues
.Cells(1).Select
Application.CutCopyMode = False

End With
End Sub


Sub GetAmounts_1()

Application.ScreenUpdating=False
On Error Resume Next

GetRange "C:\Pay Module V1.0", "A.xlsm", "Sheet1", "A1:I220", _
Sheets("Sheet15").Range("A1")

On Error GoTo 0
Application.ScreenUpdating = True

End Sub

Thanks again.
Mohamed

[HansV]

Change

Dim fn as Variant

to

Dim fn as String