MPDK166
03-22-2011, 06:10 AM
I have got a script for getting Data from a closed workbook, however, this code gets the data from a "static" filename and I want to have it as a variable. I.e. I want to us a cell value as filename.
Can somebody give the solution?
************************************************
Option Explicit
Sub GetDataDemo()
Dim FilePath$, Row&, Column&, Address$
'change constants & FilePath below to suit
'**************************************
Const FileName$ = "TEST1.xls"
Const SheetName$ = "Blad1"
'Const NumRows& = 10
'Const NumColumns& = 10
FilePath = ActiveWorkbook.path & "\"
'***************************************
DoEvents
Application.ScreenUpdating = False
If Dir(FilePath & FileName) = Empty Then
MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist"
Exit Sub
End If
' For Row = 1 To NumRows
' For Column = 1 To NumColumns
Address = Cells(2, 1).Address
Cells(2, 2) = GetData(FilePath, FileName, SheetName, Address)
Address = Cells(2, 2).Address
Cells(2, 3) = GetData(FilePath, FileName, SheetName, Address)
' Columns.AutoFit
' Next Column
' Next Row
ActiveWindow.DisplayZeros = False
End Sub
Private Function GetData(path, file, sheet, Address)
Dim Data$
Data = "'" & path & "[" & file & "]" & sheet & "'!" & Range(Address).Range("A1").Address(, , xlR1C1)
GetData = ExecuteExcel4Macro(Data)
End Function
*************************************
The file name "Test1.xls" has got to be the following cell Range("A1").select
Can somebody give the solution?
************************************************
Option Explicit
Sub GetDataDemo()
Dim FilePath$, Row&, Column&, Address$
'change constants & FilePath below to suit
'**************************************
Const FileName$ = "TEST1.xls"
Const SheetName$ = "Blad1"
'Const NumRows& = 10
'Const NumColumns& = 10
FilePath = ActiveWorkbook.path & "\"
'***************************************
DoEvents
Application.ScreenUpdating = False
If Dir(FilePath & FileName) = Empty Then
MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist"
Exit Sub
End If
' For Row = 1 To NumRows
' For Column = 1 To NumColumns
Address = Cells(2, 1).Address
Cells(2, 2) = GetData(FilePath, FileName, SheetName, Address)
Address = Cells(2, 2).Address
Cells(2, 3) = GetData(FilePath, FileName, SheetName, Address)
' Columns.AutoFit
' Next Column
' Next Row
ActiveWindow.DisplayZeros = False
End Sub
Private Function GetData(path, file, sheet, Address)
Dim Data$
Data = "'" & path & "[" & file & "]" & sheet & "'!" & Range(Address).Range("A1").Address(, , xlR1C1)
GetData = ExecuteExcel4Macro(Data)
End Function
*************************************
The file name "Test1.xls" has got to be the following cell Range("A1").select