Microsoft Office Forums - View Single Post

Filip88 · 09-23-2017, 11:07 AM

My code

Code:

   Sub data_transfer()
 
    Dim File As String
    Dim xApp As Object
 
    File = Application.GetOpenFilename("Excel Files, *.xls*")
    If File = "False" Then Exit Sub
    Set xApp = CreateObject("Excel.Application")
    xApp.Visible = True
    xApp.Workbooks.Open File
 
 
Dim i As Integer
 
 
For i = 1 To 4000
Workbooks("makro.xlsm").Worksheets("List1").Range("A" & i).Value = xApp.Workbooks(1).Worksheets(1).Range("A" & i).Value
Workbooks("makro.xlsm").Worksheets("List1").Range("B" & i).Value = xApp.Workbooks(1).Worksheets(1).Range("B" & i).Value
Workbooks("makro.xlsm").Worksheets("List1").Range("C" & i).Value = xApp.Workbooks(1).Worksheets(1).Range("C" & i).Value
Next i
 
End Sub

I have main macro and multiple workbooks with data. In main macro i have code to open new workbook and transmit its data by for loop. The thing is that each time i want to transfer data, i might chose different available workbooks.

I have attached two files macro.xlsm and data.xlsx, in the workbook macro is button and code to open file and transmit its data.