Question about linking Access to Excel.
Hello,
I have a access database that stores all the tables and the queries that I want to dump on an excel sheet. On the excel sheet, I want to create a button call Refresh Data & Calculate Report, so whenever the user opens the excel sheet and clicks on the Refrest button, the new data that has been stored in Access gets feed into the Excel. How do I set up that button in Excel and how do I create that link between Access and Excel? thanks! |
Question about linking Access to Excel.
hi
the easiest way would be to create a database query. on the menu bar.... dataget external datanew database queryfollow the wizard. for you button, say your put the query at sheet1:A1, Private Sub CommandButton1_Click() sheets("sheet1").range("A1").querytable.refresh Backgroundquery:=false End Sub once refreshed, you can add code to manipulate the imported data if needed. there are other ways to do this, this is about the simpliest. Regards FSt1 " wrote: Hello, I have a access database that stores all the tables and the queries that I want to dump on an excel sheet. On the excel sheet, I want to create a button call Refresh Data & Calculate Report, so whenever the user opens the excel sheet and clicks on the Refrest button, the new data that has been stored in Access gets feed into the Excel. How do I set up that button in Excel and how do I create that link between Access and Excel? thanks! |
Question about linking Access to Excel.
On Jul 18, 2:14*pm, FSt1 wrote:
hi the easiest way would be to create a database query. on the menu bar.... dataget external datanew database queryfollow the wizard. for you button, say your put the query at sheet1:A1, Private Sub CommandButton1_Click() sheets("sheet1").range("A1").querytable.refresh Backgroundquery:=false End Sub once refreshed, you can add code to manipulate the imported data if needed. there are other ways to do this, this is about the simpliest. Regards FSt1 " wrote: Hello, I have a access database that stores all the tables and the queries that I want to dump on an excel sheet. *On the excel sheet, I want to create a button call Refresh Data & Calculate Report, so whenever the user opens the excel sheet and clicks on the Refrest button, the new data that has been stored in Access gets feed into the Excel. *How do I set up that button in Excel and how do I create that link between Access and Excel? thanks!- Hide quoted text - - Show quoted text - Thank you! |
Question about linking Access to Excel.
You could also use adodb. Here is an example of copyfromrecordset.
Option Explicit Sub getData() 'Needs reference the Axtive X Library 2.0 or higher Const shName As String = "Sheet1" Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim fld As ADODB.Field Dim sSQL As String, sProvider, sDataSource, strConn Dim iCol As Long Dim wks As Worksheet Set wks = Worksheets(shName) 'Clear sheet before refresh wks.Cells.ClearContents 'use ACE for 2007 mdb or less 'sProvider = "Provider=Microsoft.ACE.OLEDB.4.0; " 'use Jet for 2003 mdb or less sProvider = "Provider=Microsoft.Jet.OLEDB.4.0; " 'change data source with the path to your database sDataSource = "Data Source=C:\ilsa\data\Ilsa.mdb;Persist Security Info=False" strConn = sProvider & sDataSource 'sSQL = "Replace with your query" sSQL = "SELECT AdjustLog.* FROM AdjustLog;" Set cnn = New ADODB.Connection Set rs = New ADODB.Recordset cnn.Open strConn rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic iCol = 1 For Each fld In rs.Fields wks.Cells(1, iCol) = fld.Name iCol = iCol + 1 Next wks.Range("A2").CopyFromRecordset rs rs.Close Set rs = Nothing cnn.Close Set cnn = Nothing End Sub " wrote: On Jul 18, 2:14 pm, FSt1 wrote: hi the easiest way would be to create a database query. on the menu bar.... dataget external datanew database queryfollow the wizard. for you button, say your put the query at sheet1:A1, Private Sub CommandButton1_Click() sheets("sheet1").range("A1").querytable.refresh Backgroundquery:=false End Sub once refreshed, you can add code to manipulate the imported data if needed. there are other ways to do this, this is about the simpliest. Regards FSt1 " wrote: Hello, I have a access database that stores all the tables and the queries that I want to dump on an excel sheet. On the excel sheet, I want to create a button call Refresh Data & Calculate Report, so whenever the user opens the excel sheet and clicks on the Refrest button, the new data that has been stored in Access gets feed into the Excel. How do I set up that button in Excel and how do I create that link between Access and Excel? thanks!- Hide quoted text - - Show quoted text - Thank you! |
All times are GMT +1. The time now is 03:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com