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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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

R
  • Question
Automate data pull from one file to another
Replies
1
Views
197
John_w
J
M
  • Question
Getting Power Query data to an Excel worksheet
Replies
4
Views
243
MichaelMorris
M
W
  • Question
How to add "edit text import" to get data from an external text file?
Replies
3
Views
160
waljbt
W
D
  • Question
IF THEN & Average.//Ishan
Replies
2
Views
221
Peter_SSs
Peter_SSs
M
  • Solved
Copy the last sheet from file to another with ignore empty whole column
Replies
7
Views
182
mumps
mumps

Forum statistics

Threads
1,215,326
Messages
6,124,265
Members
449,149
Latest member
mwdbActuary

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