Transferring data between Access and Excel using VBA

md85 · #1 (**permalink**) 09-03-10, 12:37

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.

Code:

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

Any help will be greatly appreciated; I'm using Access and Excel 2007. Was wondering if the record macro feature can be used in this?

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?

md85 · #2 (**permalink**) 09-03-10, 14:05

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???

Internet Services

Web Development

Digital Marketing

Consumer Tech