Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 03-10-2009
Members
 
Join Date: May 2009
Posts: 45
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.
Reply With Quote
  #2  
Old 03-10-2009
Member
 
Join Date: Mar 2009
Posts: 89
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
Reply With Quote
  #3  
Old 03-10-2009
Members
 
Join Date: May 2009
Posts: 45
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.
Reply With Quote
  #4  
Old 03-10-2009
Member
 
Join Date: Jan 2009
Posts: 141
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
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Import data from another workbook"
Thread Thread Starter Forum Replies Last Post
Import & Export Data AnkitSingh007 Off Topic Chat 2 09-12-2011 04:18 PM
How can I use VBA to copy data from the closed workbook to the open workbook on Microsoft Excel 2010? Borislav Windows Software 3 11-07-2011 10:37 AM
Extract data from one workbook to another Laler Windows Software 3 18-05-2009 04:56 PM
Copy data from one workbook to another Zool Windows Software 3 16-05-2009 09:42 PM
How to enter Data in Shared Excel Workbook Kamran Windows Software 3 30-04-2009 11:44 AM


All times are GMT +5.5. The time now is 01:58 AM.