importing data + maintaining history

[floragray]

Hi All,,

I have a bit of a problem:

I have to import data from an external source(oracle database) to an Excel(2013) table.
Now the data in the staging table in the database keeps refreshing/changing, However in Excel i need the data to come into a new row everytime instead of refreshing the whole table and looking like the staging table in the database. So basically i need to build history in Excel.
Is this possible?? And if so, Howww??

Thankssssss!!

 

[marious]

1. Use for example sheet 1 to import data. Add a column for example with header IMPORTED DATE and add =TEXT(NOW(),"mm-dd-yy hh:mm:ss"), This will help you to keep track by time. 2. Use a macro using relative positions that add the data range to the last empty row into a different sheet. Be sure that when you paste data you use Paste special and paste values only "right click mouse" so you only copy the data and not the values. 3) add a button and assign the macro to it. Now each time that you refresh the button the new data will be added at the end.

 

[floragray]

Hey Marious,

Thanks for getting back on the post! Appreciate it.

I dont know VBA myself, picked this from another post, but would the macro look something like this??? Also, if there no other way of doing this without using VBA???

Sub Macro1() 
Dim destCell As Range 
  With ActiveSheet 
    If .QueryTables.Count = 0 Then 
      Set destCell = .Range("A1") 
    Else 
      Set destCell = .QueryTables(1).ResultRange.Offset(0, 1) 
      'Either next empty column to the right 
      'Set destCell = .QueryTables(1).ResultRange.Offset(1, 0) 
      'Or next empty row below 
      .QueryTables(1).Delete 
    End If 
    With .QueryTables.Add(Connection:="URL;http://www.mrexcel.com/forum/excel-questions", Destination:=destCell) 
      .Name = "excel-questions" .RefreshStyle = xlInsertDeleteCells .AdjustColumnWidth = False .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .Refresh False 
    End With 
  End With 
End Sub

</PRE>

 

[marious]

Macros are not that difficult. At the beginning don't look at the VBA code behind. It is better to delete the macro and record it again until your are satisfied with the result . The most important thing is to understand the difference between absolute and relative position. Also copy the data set in a sandbox for practice "THERE IS NO UNDO WITH MACROS" .Watch this video from Mr. Michael Girvin and practice. Excel Magic Trick 923: Recorded Macro Basics: Click Button To Move Data To Table On Another Sheet - YouTube