Get external data

V

vsv

New Member
Joined
Apr 17, 2008
Messages
11
I have a file called daily.xls. It has a table in it with few columns with headings. I have another file called monthly.xls. This file has all the same column heading as daily.xls. I want to import the data from daily.xls to monthly.xls everyday.
The daily.xls gets updated everyday with new set of values under all columns. I want these new values to go at the bottom in monthly.xls. I do not want the old values from monthly.xls deleted.
I tried Get External Data using the query but while importing the new data from daily.xls it overwrites it on the existing rows. I do not want that.
I hope its clear enough to understand.
Thanks.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
No time to actual create the macro but you could just do a relative reference macro that you could run each time you open the daily work book.
 
Upvote 0
Hi. Please modify to suit. Regards, Fazza

Code: 
Sub test()

  Const strSOURCE_FILE As String = "D:\daily.xls"
  Const strSOURCE_SHEET_NAME As String = "Sheet1"

  Dim strConn As String
  Dim strSQL As String
  Dim objRS As Object

  strConn = Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _
      strSOURCE_FILE, ";Extended Properties=""Excel 8.0;"""), vbNullString)

  strSQL = "SELECT * FROM [" & strSOURCE_SHEET_NAME & "$]"

  Set objRS = CreateObject("ADODB.Recordset")
  With objRS
    .Open strSQL, strConn
    Cells(1, 1).End(xlDown).Offset(1).CopyFromRecordset objRS
    .Close
  End With

  Set objRS = Nothing

End Sub
 
Upvote 0
Similar but with connection object,
Code: 
Sub test2()

  Const strSOURCE_FILE As String = "D:\daily.xls"
  Const strSOURCE_SHEET_NAME As String = "Sheet1"

  Dim strConn As String
  Dim strSQL As String
  Dim objConn As Object

  strConn = Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _
      strSOURCE_FILE, ";Extended Properties=""Excel 8.0;"""), vbNullString)

  strSQL = "SELECT * FROM [" & strSOURCE_SHEET_NAME & "$]"

  Set objConn = CreateObject("ADODB.Connection")
  objConn.Open strConn
  Cells(1, 1).End(xlDown).Offset(1).CopyFromRecordset objConn.Execute(strSQL)
  Set objConn = Nothing

End Sub
 
Upvote 0
Or a little briefer,
Code: 
Sub test2()
  Const strSOURCE_FILE As String = "D:\daily.xls"
  Const strSOURCE_SHEET_NAME As String = "Sheet1"
  Dim objConn As Object

  Set objConn = CreateObject("ADODB.Connection")
  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strSOURCE_FILE & ";Extended Properties=""Excel 8.0;"""
  Cells(1, 1).End(xlDown).Offset(1).CopyFromRecordset objConn.Execute("SELECT * FROM [" & strSOURCE_SHEET_NAME & "$]")
  Set objConn = Nothing
End Sub
 
Upvote 0
PS As written, I have assumed there is some data in column A already. The End(xlDown) from cell(1,1). If you start with only the header - and no data in monthly - you might prefer to use xlUp from the last row. I have used "SELECT *" in the SQL, modify to suit if you need to. F
 
Upvote 0
Fazza,
Thanks for the interest in my querry.
I did not understand your last comment. can you please elaborate on that!!!
 
Upvote 0
Sure. The posted SQL uses "SELECT *". This returns all fields.

As opposed to listing the specific fields, such as "SELECT year, category, department, cost"

HTH, F
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Table Keeps Referencing External Source
Replies
3
Views
200
Alex Blakenburg
A
D
  • Question Question
Embedded data from external links
Replies
2
Views
170
jkpieterse
jkpieterse
K
  • Question Question
Import Data from Multiple Web Pages
Replies
0
Views
253
Ka001
K
M
  • Solved
pivot table external source
Replies
5
Views
658
alansidman
A
N
  • Question Question
How to import specific data from Windows Explorer details list? (Image focal length information)
Replies
1
Views
127
felixstraube
F

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top