Excel Data Refresh!!!!

[arpitraval]

Hi I am simply working on MS Access Database. Created one table and exported to the excel. Now each time if i append the latest data to that table which i have created in Access then I want excel worksheet to be refreshed when i hit the refresh button in excel.

What to do in that case!!!!

 

[boblarson]

You would really need to link the Excel worksheet to Access for this (instead of exporting to Excel).

 

[arpitraval]

Thanks

Can you tell me how to do that!! I am working on that right now but not getting the point...
Here is what I have done so far,
open that excel worksheet --- then hit the data tab and went in to data connection there and after that i just set the new connection with that Access Database.---- After that there is one more thing below which says that " click to see where this selected connections are used " . When I press then it came up with one message " connection not used in this workbook"...

Thanks

 

[arpitraval]

Still working on it and waiting for your replies too..!

 

[boblarson]

Which version of Excel are you using?

 

[arpitraval]

I am using 2007

 

[boblarson]

I am using 2007

Okay, I'll have to wait until I'm home to give you instructions on that as it is quite a bit different in where you find things as compared to 2003 which I have here at work.

So, if anyone else wants to give it a go before I can do it, feel free.

 

[boblarson]

Hope this helps:

 

[qafself]

Nice one Bob

That's got to be one of the clearest answers I've ever seen on the boards!

 

[arpitraval]

Bob that helps me a lot. Sorry for late reply but that was really nice... Thanks

 

[chaostheory]

If you know anything about VBa or want to do it that way this should work

You have to change the path to database and table name listed in red below to your own paths and table name.

Sub PullDataIn()
'
' RetrieveData Macro
' Pulls data from a query
'
Dim varConn As String
Dim varSQL As String
 
varConn = "ODBC;DBQ=[COLOR=red]Path to database[/COLOR].mdb;Driver={Driver do Microsoft Access (*.mdb)}"
 
varSQL = "SELECT * FROM ""[COLOR=red]Table Name[/COLOR]"""
         With ActiveSheet.QueryTables.Add(Connection:=varConn, Destination:=Range("A1"))
            .CommandText = varSQL
            .Name = "Query-39008"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = [COLOR=red]True [/COLOR][COLOR=black]'This is the part that refreshes on open[/COLOR]
            .BackgroundQuery = True
            .RefreshStyle = xlOverwriteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .Refresh BackgroundQuery:=[COLOR=black]False[/COLOR]
        End With
End Sub

 

[blesson]

Dear Bob,
Your post is helped me a lot.. good job.
I have one more doubt
when i reach the third section ie Select table, on that list not all the tables and queries are displaying. Can please help me on this