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

Sponsored Links



How to move data from one excel spreadsheet to another automatically?

Software Development


Reply
 
Thread Tools Search this Thread
  #1  
Old 26-02-2009
Member
 
Join Date: Feb 2009
Posts: 2
How to move data from one excel spreadsheet to another automatically?
  

Hi,

I want to know if we can move data from one excel spreadsheet to another is possible or not? Do I need any VB or .net program for this or not?

Please guide how to do this?

How to move data from one excel spreadsheet to another automatically?

Reply With Quote
  #2  
Old 26-02-2009
Member
 
Join Date: May 2008
Posts: 115
Re: How to move data from one excel spreadsheet to another automatically?

Here's the code I would use:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if the Target is in
'Column 6 and contains data
If Target.Column = 6 And Target.Cells.Count = 1 Then
If Target.Value <> "" Then
'Store Target Address
MyAddress = Target.Address
'Find Next Empty Cell In Sheet2 Column A
NextRow = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
'Cut and Paste Target Row to Sheet2
Target.EntireRow.Cut Destination:=Sheets(2).Range("A" & NextRow)
'Delete Target Row in Sheet1
Range(MyAddress).EntireRow.Delete
End If
End If
End Sub
Reply With Quote
  #3  
Old 26-02-2009
Member
 
Join Date: May 2008
Posts: 2,007
Re: How to move data from one excel spreadsheet to another automatically?

Here you need to define is a common path where you are storing the database and all the users should have access to the folder where database is saved...please find enclosed macro,your file which is working and database...

Code:
 
Sub Submit_Request()
   Dim objForm As Worksheet, objRequests As Worksheet
   Dim targetrow As Range
   Dim DAOdBase As DAO.Database
   Dim DAORecSet As DAO.Recordset
   Dim xRow As Long, xCol As Integer
   Dim xDelete As String, exrange As Range
   Dim i As Long
   
   
   
   
   
    Set objForm = Sheets("Enter")
    
    If Trim(objForm.Cells(7, "D").Value) = "" Then
        objForm.Cells(7, "D").Select
        MsgBox "Please enter the Card number"
        Exit Sub
    End If
    
    If Trim(objForm.Cells(5, "H").Value) = "" Then
        objForm.Cells(5, "H").Select
        MsgBox "Please enter Client"
        Exit Sub
    End If
    
    If Trim(objForm.Cells(7, "H").Value) = "" Then
        objForm.Cells(7, "H").Select
        MsgBox "Please enter User"
        Exit Sub
    End If
        
    If Trim(objForm.Cells(9, "D").Value) = "" Then
        objForm.Cells(9, "D").Select
        MsgBox "Please enter Option 1"
        Exit Sub
    End If
    
    If Trim(objForm.Cells(11, "D").Value) = "" Then
        objForm.Cells(11, "D").Select
        MsgBox "Please enter Requested by"
        Exit Sub
    End If
    
    If Trim(objForm.Cells(13, "D").Value) = "" Then
        objForm.Cells(13, "D").Select
        MsgBox "Please enter some comments"
        Exit Sub
    End If
    
    If Trim(objForm.Cells(5, "L").Value) = "" Then
        objForm.Cells(5, "L").Select
        MsgBox "Please enter status"
        Exit Sub
      ElseIf UCase(Trim(objForm.Cells(5, "L").Value)) = "RESOLVED" Then
         ' determine target sheet
          Set objRequests = Sheets("Resolved")
      Else
          Set objRequests = Sheets("Pending")
    End If
    On Error Resume Next
    Application.EnableEvents = False
    Set targetrow = objRequests.Cells(objRequests.Cells(6000, "A").End(xlUp).Row + 1, "A")
    
    With targetrow
      .Offset(0, 0).Value = objForm.Cells(22, "D").Value
      .Offset(0, 1).Value = objForm.Cells(5, "D").Value
      .Offset(0, 2).Value = objForm.Cells(7, "D").Value
      .Offset(0, 3).Value = objForm.Cells(5, "H").Value
      .Offset(0, 4).Value = objForm.Cells(7, "H").Value
      .Offset(0, 5).Value = objForm.Cells(9, "D").Value
      .Offset(0, 6).Value = objForm.Cells(9, "H").Value
      .Offset(0, 7).Value = objForm.Cells(11, "D").Value
      .Offset(0, 8).Value = objForm.Cells(11, "H").Value
      .Offset(0, 9).Value = objForm.Cells(13, "D").Value
      .Offset(0, 11).Value = objForm.Cells(5, "L").Value
   End With
 
  i = objRequests.Cells(65536, "a").End(xlUp).Row
   Set k = objRequests.Range("A" & i & ":N" & i)
   ActiveWorkbook.Names.Add Name:="Raw1", RefersTo:=k
  
Application.EnableEvents = True
xpath = "Your Path where database is saved\db1.mdb"
 
Set DAOdBase = DBEngine.OpenDatabase(xpath)
 
xrange = "Raw1"
Set exrange = Range(xrange)
 
xtable = "btable"
 
 
Set DAORecSet = DAOdBase.OpenRecordset(xtable)
 
For xRow = 1 To exrange.Rows.Count
    DAORecSet.AddNew
    For xCol = 1 To exrange.Columns.Count
        DAORecSet.Fields(xCol) = exrange.Cells(xRow, xCol).Value
    Next xCol
    DAORecSet.Update
Next xRow
 
 
 MsgBox "Your Complaint Ref is " & objForm.Cells(22, "D")
    
    ' Now clear the form
    objForm.Cells(7, "D").Value = ""
    objForm.Cells(9, "D").Value = ""
    objForm.Cells(5, "H").Value = ""
    objForm.Cells(7, "H").Value = ""
    objForm.Cells(9, "H").Value = ""
    objForm.Cells(11, "D").Value = ""
    objForm.Cells(19, "E").Value = ""
    objForm.Cells(11, "H").Value = ""
    objForm.Cells(13, "D").Value = ""
    objForm.Cells(5, "L").Value = ""
 
    
End Sub
Reply With Quote
  #4  
Old 26-02-2009
Member
 
Join Date: Aug 2008
Posts: 474
Re: How to move data from one excel spreadsheet to another automatically?

i got kinda confused....

but you should be able to just highlight the range you want moved (B4:G200), right click and choose "Move", and it should prompt you to choose either "new worksheet" or "new workbook"...im not exactly sure what it asks you. either that, or just try copy and paste. oh, and to move only certain rows, highlight the first one you want moved, hold down "ctrl" and highlight the rest of the rows, then do the first steps i told.
Reply With Quote
  #5  
Old 26-02-2009
Member
 
Join Date: Jun 2008
Posts: 144
Re: How to move data from one excel spreadsheet to another automatically?

Please have a look at this
http://www.office-addins.com/-excel-...assistant.html
I hope this helps you!
Reply With Quote
  #6  
Old 26-02-2009
Member
 
Join Date: Jun 2008
Posts: 97
Re: How to move data from one excel spreadsheet to another automatically?

Make a copy of the worksheet (select the sheet, right click on the sheets tab, select the "Move or Copy" option, and check the "Create a copy" option. On the new created sheet select all cells (it should have all the data on) select all the cells (click on the square on the left of the column A, and above the 1st Row heading. Right click \Copy, right click paste special and select value or values and number format!
Reply With Quote
Reply

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



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to move data from one excel spreadsheet to another automatically?"
Thread Thread Starter Forum Replies Last Post
What is the formula to copy or move a spreadsheet vALaNCiA MS Office Support 1 02-02-2012 03:53 PM
cannot copy data from one spreadsheet to another fANIBHUSAN Windows Software 4 31-03-2011 08:15 PM
Excel 2010 spreadsheet automatically create and rename cHYNA Windows Software 4 15-01-2011 02:12 AM
How to Import all text data in Excel spreadsheet racer Tips & Tweaks 1 06-01-2010 09:38 PM
Export data from excel spreadsheet to an Oracle Table Kurtz Windows Software 2 13-05-2009 01:11 PM


All times are GMT +5.5. The time now is 07:18 PM.