09-03-10, 12:37 #1Registered User
- Join Date
- Jun 2010
Unanswered: Transferring data between Access and Excel using VBA
Hey all, have been designing a database for Oil and Gas companies, which is supposed to hold their Stock market information plus any financial information of the company. The back-end of the database is in Access and the front-end is in Excel.
Im fairly new to VBA, but have managed to write a code to take care of a number of queries to do certain calculations and generate a number of tables in Access.
Now there are two things that need to be achieved with regards to this;
1. Firstly, the access side of the database needs to be updated daily from the Excel side; this is because I have found an add-on in Excel which can download the latest StockMarket data for the specific companies; the information from there goes into a table in access called Historical_Stock_Data into the 4 following columns; StockCode, Dates, SharePrice, Volume.
2. Secondly, the tables generated for the report in Access, need to be transferred over into specific worksheets and specific cells in Exce, without the cells losing any of their format properties(For example; in the % change column, it needs to show the colour depending on if the change is positive or negative). I have some simple code written for this purpose but need some help with this. Following is the code for this part in Excel.
Option Explicit Public Const DataLocation As String = "C:\Documents and Settings\Alice\Desktop\Database\TestDatabase2.accdb" Sub Market_Update() Call ImportFromAccessTable(DataLocation, "Final_Table", Worksheets(2).Range("B8")) End Sub Sub ImportFromAccessTable(DBFullName As String, TableName As String, TargetRange As Range) Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer Set TargetRange = TargetRange.Cells(1, 1) ' open the database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & DBFullName & ";" Set rs = New ADODB.Recordset With rs ' open the recordset ' .Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable ' all records .Open "SELECT * FROM Final_Table", cn, , , adCmdText ' filter records For intColIndex = 0 To rs.Fields.Count - 1 ' the field names TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name Next TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data End With rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub Sub Company_Information() Dim companyName As String On Error GoTo gotoError companyName = Application.InputBox(Prompt:="Enter Company Name", _ Title:="Company Name", Type:=2) Exit Sub 'Don't execute errorhandler at end of routine gotoError: MsgBox "An error has occurred" End Sub
Edit: Would like to add that, to be able to save the formatting of the cells is very important, is it better to do that by code or is there a way to save the formatting of the cells once the data has been imported into Excel?
Last edited by md85; 09-03-10 at 13:18.
09-03-10, 14:05 #2Registered User
- Join Date
- Jun 2010
Hey all, as you can tell from the code above, I know how to place specfic tables in specific cells, but the problem I'm having now is that every time the information is put into Excel the formatting of the cells goes away. Then when I press enter in the fx bar it comes back; it would be a rather tedious process to do it like that, though; should I maybe use a recorded macro which does that after the update into excel???