CODEWITHSUNDEEP
excelvba
A K
A K

Reputation: 27

VBA - Pulling data from one file to another

I'm trying to create a VBA script that goes into file1 and copies the data into file2. File 1 contains the data.

The issue I'm having is file2 has more columns and not necessarily in the same order as the ones in file1. As well, the Range is wrong, I'm not sure how to select all relevant data. How do i make sure it gets all the relevant rows per column in file1?

Sub GetDatacClosedBook()

Dim src As Workbook
Set src = Workbooks.Open("C:\Users\Data\Documents\File1", True, True)
Set wbOpen = ActiveWorkbook

'this is the workbook in which the data will be transferred to
Workbooks.Open "C:\Users\Data\Documents\file2.xlsx"

Worksheets("Sheet1").Range("A1:D3").Formula = src.Worksheets("Sheet1").Range("A1:D3").Formula
wbOpen.Close

End Sub

Upvotes: 0

Views: 651

Answers (2)

Gen.L
Gen.L

Reputation: 402

You should first figure out the columns in your data sheet match which columns in your destination sheet. And then everything should be easy. This can be done in multiple way. I assume your A row has the headers, then you can match the column by match the headers

Sub Macro()
    Dim destSht As Worksheet, srcSht As Worksheet
    Dim src_ColCnt As Integer, dest_ColCnt As Integer
    
    'Open the workbooks and grab the sheet reference, assign it to a worksheet variables
    Set srcSht = Workbooks.Open("D:\data.xlsx").Sheets("Sheet1")
    Set destSht = Workbooks.Open("D:\report.xlsx").Sheets("Sheet1")
    
    'Find how many columns in your destination sheet, how many columns in your source sheet and how many rows the source sheet data has.
    dest_ColCnt = destSht.Range("A1").End(xlToRight).Column
    src_ColCnt = srcSht.Range("A1").End(xlToRight).Column
    src_RCnt = srcSht.Range("A1").End(xlDown).Row - 1
    
    
    'The code below is basically loop over the source sheet headers, and for each header
    'find the column in your destination that has the same header
    'And then assign the data row by row once it knows which column in the data sheet go to which column in the destination sheet
    For i = 1 To src_ColCnt
        Header = srcSht.Cells(1, i)
        For j = 1 To dest_ColCnt
            If destSht.Cells(1, j).Value = Header Then
                For r = 1 To src_RCnt
                    'Do your assignment here row by row
                    'You can assign formula, value or different thing based on your requirement
                    'I assume your data start from the second row here
                    destSht.Cells(r + 1, j).Value = srcSht.Cells(r + 1, i).Value
                Next r
            End If
        Next j
    Next i
End Sub

This is not elegant but should give you the idea. To make the above more elegant, There are a couple of things you can use. One, using Scripting.Dictionary data structure to hold the headers in the dictionary as key, the column ordinal as the value. And then you loop your destination sheet column by column. Retrieve the right column ordinal from the dictionary. Two, you can use WorksheetFunctions.Match() to find the ordinal. Or even better if you know the order by yourself. You can just hard coding an order Array, like mapOrder = Array(3,1,5,6) and just use this array to match the column.

Upvotes: 1

Samuel Everson
Samuel Everson

Reputation: 2102

You could write a function that points to a specific workbook, locates a column -perhaps by heading- and captures that columns data into an Array which is returned by the function. Then write the arrays in the desired order to the other sheet.

Example for the Subroutine and the function:

Private Sub GetDatacClosedBook()

Dim ExampleArray As Variant
Dim Destination As Range

    ExampleArray = LocateColumnReturnArray(ThisWorkbook.Sheets("Sheet1"), "Value to find in row1 of the desired column")
    
    
    Set Destination = ThisWorkbook.Sheets("Sheet2").Range("A1")
    Destination.Resize(UBound(ExampleArray), 1) = ExampleArray

End Sub

Public Function LocateColumnReturnArray(ByRef TargetWorksheet As Worksheet, ByVal TargetColumnHeader As String) As Variant

Dim LastUsedColumn As Long
Dim TargetCell As Range

    With TargetWorksheet
        LastUsedColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
        
        For Each TargetCell In .Range(.Cells(1, 1), .Cells(1, LastUsedColumn))
            If TargetCell.Value = TargetColumnHeader Then
                LastUsedRow = .Cells(.Rows.Count, LastUsedColumn).End(xlUp).Row
                LocateColumnReturnArray = .Range(.Cells(2, TargetCell.Column), .Cells(LastUsedRow, TargetCell.Column))
                Exit Function
            End If
        Next TargetCell
    End With
End Function

You can take this concept and apply it to your requirements. This function could be run as many times as required for each column you want the data for. You would need to also specify the target for each column of data but you could modify the above to use a loop based on the columns your data is being written to.

Upvotes: 0

Related Questions