cam5778
01-21-2016, 01:47 PM
Hi,
I am currently attempting to write a VBA code to import data from a workbook. My goal is to import the data into one list from different tabs from specific rows. I don't take credit for I started, I found it on another forum.
What I need: I need it to include data from Obligations and Accruals tab. Currently, it is producing the data I need from the Disbursements tab. From the Obligations tab, I need Data from D3 down 146 rows. From the Accruals tab, I need the Data from D3 down 143 rows. I would like to combine all this data into one list from the import.
Here is what I have:
Sub TestCode()
Dim FilePath$, Row&, Column&, Address$
Const FileName$ = "Test Matrix"
Const SheetName$ = "Disbursements"
Const NumRows& = 338
Const NumColumns& = 1
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("D3").Address(, , xlR1C1)
GetData = ExecuteExcel4Macro(Data)
End Function
Thank You for any help!
Best Regards,
Chris
I am currently attempting to write a VBA code to import data from a workbook. My goal is to import the data into one list from different tabs from specific rows. I don't take credit for I started, I found it on another forum.
What I need: I need it to include data from Obligations and Accruals tab. Currently, it is producing the data I need from the Disbursements tab. From the Obligations tab, I need Data from D3 down 146 rows. From the Accruals tab, I need the Data from D3 down 143 rows. I would like to combine all this data into one list from the import.
Here is what I have:
Sub TestCode()
Dim FilePath$, Row&, Column&, Address$
Const FileName$ = "Test Matrix"
Const SheetName$ = "Disbursements"
Const NumRows& = 338
Const NumColumns& = 1
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("D3").Address(, , xlR1C1)
GetData = ExecuteExcel4Macro(Data)
End Function
Thank You for any help!
Best Regards,
Chris