Excel VBA that will get the specific data from different workbook

Question

Sub VBA_Read_External_Workbook()
'''''Define Object for Target Workbook
Dim Target_Workbook As Workbook
Dim Source_Workbook As Workbook
Dim Target_Path As String

'''''Assign the Workbook File Name along with its Path
'''''Change path of the Target File name
Target_Path = "C:\Users\User\Desktop\Excel VBA\Working Sample Folder\MAY 2017 Summary- Atlas work.xlsx"
Set Target_Workbook = Workbooks.Open(Target_Path)
Set Source_Workbook = ThisWorkbook

'''''With Target_Workbook object now, it is possible to pull any data from it
'''''Read Data from Target File
Target_Data = Target_Workbook.Sheets(1).Range("A1:B3")
Source_Workbook.Sheets(2).Range("A1:B3") = Target_Data


'''''Update Target File
Source_data = Source_Workbook.Sheets(1).Range("A1:B3")
Target_Workbook.Sheets(1).Range("A1:B3") = Source_data


'''''Close Target Workbook
Source_Workbook.Save
Target_Workbook.Save
Target_Workbook.Close False

'''''Process Completed
MsgBox "Task Completed"
End Sub

I've been modifying this code that i found in this website to use it for copying the specific data in a specific format. What I need some guidance is to add a loop to get the data from the files that will be put in a folder.Files that needed to be read

So my questions

Basically, I already set the specific range of data that needed to be copied and paste on my destination files. But instead of keep changing the target path, is there a way to put a loop that it will auto jump to the next workbook and get those values?
I found out that using this method to transfer the data, it doesn't transfer the data nature which for example if it is in time format at the source file, when the VBA execute and update the destination file, the value is not in the same format and all are pasted in general format.
Is it possible to loop the update where it will auto jump to the next row to paste the data?

I tried to google some of the VBA codes but the answer is very vague.

Appreciate any input from your experiences.

score 2 · Answer 1 · answered Jun 02 '17 at 07:39

1)Basically, I already set the specific range of data that needed to be copied and paste on my destination files. But instead of keep changing the target path, is there a way to put a loop that it will auto jump to the next workbook and get those values?

This will get you started

Dim MyFolder As String
Dim StrFile As String
Dim flName As String

'~~> Change this to the relevant folder
MyFolder = "c:\MyFolder\"
StrFile = Dir(MyFolder & "*.xls*")

'~~> Loop through all excel files in the folder
Do While Len(StrFile) > 0
    flName = MyFolder & StrFile

    '~~> Open the workbook
    Set wb = Workbooks.Open(flName)

    '
    '~~> Rest of your code
    '

    wb.Close (False)

    StrFile = Dir
Loop

2)I found out that using this method to transfer the data, it doesn't transfer the data nature which for example if it is in time format at the source file, when the VBA execute and update the destination file, the value is not in the same format and all are pasted in general format.

The code is directly setting the value and hence the formats are not copied. You need to .Copy and .Pastespecial instead of directly setting the value. Record a macro to see how .Copy and .Pastespecial work. or read up on

Range.PasteSpecial Method (Excel)

3)Is it possible to loop the update where it will auto jump to the next row to paste the data?

Find the last row and then do a copy paste to that row. Please see the below link to find the last row.

Finding Last Row

Excel VBA that will get the specific data from different workbook

1 Answers1