importing data + maintaining history

floragray

New Member
Joined
Jul 22, 2013
Messages
19
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

Board Regular
Joined
Mar 3, 2013
Messages
217
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

New Member
Joined
Jul 22, 2013
Messages
19
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???


Code:
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>
 
Last edited by a moderator:

marious

Board Regular
Joined
Mar 3, 2013
Messages
217
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
 

Forum statistics

Threads
1,081,691
Messages
5,360,644
Members
400,591
Latest member
badgergurl

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top