gimli
03-23-2010, 10:07 AM
Hi all,
Looking for some help with importing data from a file. I found some code that works posted below. Is there a way to specify what sheet the data gets imported too? And also a starting point to populate the data?
Ex:
Import data from test.xls and populate in sheet 3 starting at A4? Right now it just populates the sheet that is open when I run the macro. I have a macro button on sheet 1 and I want it to populate on sheet 3 starting at cell A4.
Thanks in advance
Sub GetData()
Dim FilePath$, Row&, Column&, Address$
'change constants & FilePath below to suit
'***************************************
Const FileName$ = "test.xls"
Const SheetName$ = "sheet 1"
Const NumRows& = 25
Const NumColumns& = 27
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(Row, Column).Address
Cells(Row, Column) = 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
Edit: VBA tags added to code
Looking for some help with importing data from a file. I found some code that works posted below. Is there a way to specify what sheet the data gets imported too? And also a starting point to populate the data?
Ex:
Import data from test.xls and populate in sheet 3 starting at A4? Right now it just populates the sheet that is open when I run the macro. I have a macro button on sheet 1 and I want it to populate on sheet 3 starting at cell A4.
Thanks in advance
Sub GetData()
Dim FilePath$, Row&, Column&, Address$
'change constants & FilePath below to suit
'***************************************
Const FileName$ = "test.xls"
Const SheetName$ = "sheet 1"
Const NumRows& = 25
Const NumColumns& = 27
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(Row, Column).Address
Cells(Row, Column) = 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
Edit: VBA tags added to code