Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2010
    Posts
    58

    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.

    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?
    Last edited by md85; 09-03-10 at 13:18.

  2. #2
    Join Date
    Jun 2010
    Posts
    58
    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???

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •