Import Daily Data into Excel Columns

[Tai]

Hi Excel Gurus--
I am trying to import daily GL balances into Excel for analysis. I'd
like to keep all the daily balances in separate column (1-31). Right
now I just right-click and refresh the data, but it override yesterday
balance. To prevent this I have to copy yesterday balance to another
column. Is there a way to have Excel refresh data by insert a new
column and push the previous data one column to the right? This way I
can keep all my 30 days balances.
Thank you for your help.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins &
Software!http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[Don Guillett]

Do you have code you can post?

 

[Tai]

There is no code. Maybe this will help. The data I imported by go to
DATA - IMPORT EXTERNAL DATA - NEW DATABASE QUERY. After connect to the
database, I select the account number and the balance fields to import.
Every day, I right-click the spreadsheet and select REFRESH.
Unfortunately, this will override the previous day balances and I'd to
keep all 30days balances to do the analysis. Hope this will help.
Thanks.
Tai

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins &
Software!http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[Don Guillett]

Then I would suggest a macro that copies the data to a column in ANOTHER
worksheet before refresh. UNTESTED

Sub copycol() ' Before Refresh
With Sheets("destinationsheet")
x = .Columns.Count + 1
Sheets("datasheet").Columns(1).Copy _
..Columns (x)
End With
Sheets("datasheet").QueryTables(1).Refresh
End Sub

 

[Tai]

I tried to run your code and got an error message "object required".
The datasheet column got marching ants around the column to be copied
but that is far as it went. What am I missing? (I've never write code
before so I don't exactly know what it is asking me).


** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins &
Software!http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[Don Guillett]

try this instead
..Columns (lc=cells(1,columns.count).end(xltoleft).columns+1)
Sub copycol() ' Before Refresh
With Sheets("destinationsheet")
Sheets("datasheet").Columns(1).Copy _

..Columns (cells(1,columns.count).end(xltoleft).columns+1)
End With
Sheets("datasheet").QueryTables(1).Refresh
End Sub

 

[Tai]

Thanks for your help, Don. I tried the code but it does not work. VBA
is beyond me so I think I just copy and paste. Thanks for your help.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[Don Guillett]

Send me the workbook and I will have a look.

 

[h1]

record a macro of what you have been doing so far. e.g. if yesterday's
data are in collumn a select col a plus the 30 col to the left (so it
will work on the 31st, too!). then select cell b1 (or below if there is
a header) and paste special, values only. then refresh. now stop
recording. there is your code.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **