Import data from another workbook
I have created a workbook in Excel 2007 and in that i need to add data on a daily basis from another workbook, early i used to copy paste from one workbook to another, when i told my friend about it he told me why don't i use macro to copy data from one workbook to another, in the way it is being paste to the end of workbook in order to preserve the historical data i already got in another workbook. Does anyone know what would be the code for it.
Import data from another workbook
Before trying out any code make sure you give proper path for files which are being used because it might happen that if you have provided wrong path then your code won't work. Once you have done with it try to use the below code I'm sure with this code you will be able to import data from another workbook.
Code:
Sub Work(ByVal Tgt_Name As String, ByVal Src_Name As String)
Dim TgtWbk As Workbook, SrcWbk As Workbook, WS As Worksheet
If FileExists(Tgt_Name) Then
' Target exists so open it.
Set TgtWbk = Workbooks.Open(Filename:=Tgt_Name)
MsgBox "Kool : True"
Else
' Target does not exist so create it.
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Tgt_Name
Set TgtWbk = ActiveWorkbook
MsgBox "Kool : False"
End If
'Open the Source Workbook
Set SrcWbk = Workbooks.Open(Filename:=Src_Name, ReadOnly:=True)
' Run through all sheets in the source work ook
i = 1
For Each WS In SrcWbk.Worksheets
If WksExists(TgtWbk, WS.Name) Then
Status Src_Name, WS.Name, "Worksheet already exists in " & Tgt_Name & ", Skipped."
MsgBox "Kool : True, No:" & i
Else
TgtWbk.Worksheets.Add After:=TgtWbk.Worksheets(TgtWbk.Worksheets.Count)
TgtWbk.ActiveSheet.Name = WS.Name
CopySheet WS, TgtWbk.ActiveSheet
Status Src_Name, WS.Name, "Worksheet copied to Target."
MsgBox "Kool : False, No:" & i
End If
i = i + 1
Next WS
' Close the Source, no need to save
SrcWbk.Close SaveChanges:=False
Set SrcWbk = Nothing ' Correctly set the SrcWbk object to Nothing
' Do the same for the Target
TgtWbk.Close SaveChanges:=True
Set TgtWbk = Nothing
End Sub
Import data from another workbook
Thanks for replying me , when i am trying to run my macro i am getting some kind of error message it seems that there is some issue with code. When i click on Run button the vba code view is then switched to sheet 2, rather then staying on sheet 1.
Import data from another workbook
Try to copy & paste the code in VBA where you wish to copy data from WorkBook("Blank1").Sheets("Sheet2") to WorkBook("Blank2").Sheets("Sheet1") which already stores some data:
Code:
Sub Copydata()
Dim WS_1 As WorkSheet, WS_2 As WorkSheet
Set WS1 =WorkBook("Blank1").Sheets("Sheet2")
Set WS2 =WorkBook("Blank2").Sheets("Sheet1")
WS1.UsedRange.Copy WS2.Cells.(Rows.Count, "A").end(xlUp)
End Sub