Excel-Access Integration (1 Viewer)

[md85]

Hey all, have been working on designing a new database for work. They have been using Excel for their daily reports and all the data is stored in there, so I decided to have the back-end of the database in Access and the front-end in Excel, so any analytical work can be easily performed once all the data has been imported into Excel.

Now I'm fairly new to VBA, slowly getting used to using it, have written some code to transfer one of the calculated tables from Access to Excel:

[B][B]Option Explicit
Public Const DataLocation As String = "C:\Documents and Settings\Alice\Desktop\Database\TestDatabase21.accdb"
Sub Market_Update()
Call ImportFromAccessTable(DataLocation, "Final_Table", Worksheets(2).Range("A5"))
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[/B][/B]

I've got two questions regarding this:



Firstly, the above works fine, the only issue I've been having is maintaining the Cell formatting for the cells where the Values are pasted into; so for example when the percentage change value is pasted into it's specific cell I have ([Green] "+"0.0%;[Red]"-"0.0%) to format the cells into the right colour; but the formatting is not applied to the values till I click on the cell and press Enter; and even then if it is '3.57' it'll turn into '+357%'. Not sure how to tackle this.

Secondly, I have an add-on for Excel, which updates the Stockmarket values automatically and is on a Sheet of it's own in the workbook; I have an UPDATE button on the main page of the worksheet, now when I press that UPDATE button; two things need to happen, firstly the add-on needs to update all the StockMarket values on the sheet and then, it needs to transfer all those values from Excel to a specific table in Access. For this part all the column names in Excel and Access match, so it would be an easy transfer, just again am fairly new to this so am not sure what way to do it.

Any help would be greatly appreciated. Thank you.

 

[chergh]

Firstly, the above works fine, the only issue I've been having is maintaining the Cell formatting for the cells where the Values are pasted into; so for example when the percentage change value is pasted into it's specific cell I have ([Green] "+"0.0%;[Red]"-"0.0%) to format the cells into the right colour; but the formatting is not applied to the values till I click on the cell and press Enter; and even then if it is '3.57' it'll turn into '+357%'. Not sure how to tackle this.

Is the formatting applied before or after pasting the data? If you currently do it before pasting the data try changing it to after you paste the data.

As for you second issue use something like:

worksheets("SheetName").Range("Whatever the range is").numberformat = "General/%"

Secondly, I have an add-on for Excel, which updates the Stockmarket values automatically and is on a Sheet of it's own in the workbook; I have an UPDATE button on the main page of the worksheet, now when I press that UPDATE button; two things need to happen, firstly the add-on needs to update all the StockMarket values on the sheet and then, it needs to transfer all those values from Excel to a specific table in Access. For this part all the column names in Excel and Access match, so it would be an easy transfer, just again am fairly new to this so am not sure what way to do it.

Any help would be greatly appreciated. Thank you.

Create a named range in excel for the table then you should be able to either add it as a linked table to access and run a query from there to import the data from it or write a sql statement in excel and import it into access that way.

 

[md85]

The formatting is applied before the data is pasted in; but thats because I have this worksheet saved and all the cell formatting has been done before-hand;(Without using VBA; it's done by using the right click Format Cells.. option).

So the place where the data is being pasted in is a kind of report that is ready with all the cells formatted, if I were to do it after the data is pasted in, it would take an awfully long time to get it done.

As I said, not too experienced with VBA but a fast learner, so if there is a way to format around 600 odd cells using VBA, I don't mind writing the code but I would definately prefer doing it the other way if possible.

For the second part, I do have a named range ready; not sure what sort of SQL statement would work for that purpose; have got some VBA code which I think could work, but keep getting an error with it at the moment; if you could check it out:

Sub UPDATE()

Dim cnt As ADODB.Connection
Dim stSQL As String, stCon As String, DataLocation As String
Dim stSQL2 As String

'database path - currently same as this workbook
DataLocation = ThisWorkbook.Path & DataLocation
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DataLocation & ";"
'SQL code for GL Insert to Access
stSQL = "INSERT INTO Historical_Stock_Data SELECT * FROM [Portfolio] IN '" _
& ThisWorkbook.FullName & "' 'Excel 8.0;'"

'set connection variable
Set cnt = New ADODB.Connection
'open connection to Access db and run the SQL
With cnt
    .Open stCon
    .CursorLocation = adUseServer
    .Execute (stSQL)
End With
'close connection
cnt.Close

'release object from memory
Set cnt = Nothing


End Sub

 

[chergh]

The formatting is applied before the data is pasted in; but thats because I have this worksheet saved and all the cell formatting has been done before-hand;(Without using VBA; it's done by using the right click Format Cells.. option).

So the place where the data is being pasted in is a kind of report that is ready with all the cells formatted, if I were to do it after the data is pasted in, it would take an awfully long time to get it done.

As I said, not too experienced with VBA but a fast learner, so if there is a way to format around 600 odd cells using VBA, I don't mind writing the code but I would definately prefer doing it the other way if possible.

To format the cells you could add something like:

dim ws as worksheet
set ws = targetrange.parent

ws.range("B2:B" & ws.rows.count).numberformat = "[Green] ""+""0.0%;[Red]""-""0.0%"

Just replace the B with whatever column the data is in.

 

[md85]

Cool, thanks! Will check that out; seems simple enough.

Also forgot to add earlier on; the worksheet where the values for the Access table are taken from, has a formulae in the cells which comes up with a value once its applied, so how to paste just the VALUES?

 

[chergh]

For the second part, I do have a named range ready; not sure what sort of SQL statement would work for that purpose; have got some VBA code which I think could work, but keep getting an error with it at the moment; if you could check it out:

Sub UPDATE()

Dim cnt As ADODB.Connection
Dim stSQL As String, stCon As String, DataLocation As String
Dim stSQL2 As String

'database path - currently same as this workbook
DataLocation = ThisWorkbook.Path & DataLocation
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DataLocation & ";"
'SQL code for GL Insert to Access
stSQL = "INSERT INTO Historical_Stock_Data SELECT * FROM [Portfolio] IN '" _
& ThisWorkbook.FullName & "' 'Excel 8.0;'"

'set connection variable
Set cnt = New ADODB.Connection
'open connection to Access db and run the SQL
With cnt
    .Open stCon
    .CursorLocation = adUseServer
    .Execute (stSQL)
End With
'close connection
cnt.Close

'release object from memory
Set cnt = Nothing


End Sub

It's a long time since I did this. I will need to read upon it before I can give you an answer to do it from excel. It would be easier to do this from access than it would be from excel.

To do it from access you would create a linked table from the named range in the excel workbook and then it is a simple INSERT INTO statement to append the data.

 

[chergh]

Cool, thanks! Will check that out; seems simple enough.

Also forgot to add earlier on; the worksheet where the values for the Access table are taken from, has a formulae in the cells which comes up with a value once its applied, so how to paste just the VALUES?

I wouldn't worry about the values being calculated from formulas when you append to access it will append the values not the formulas.

 

[chergh]

Also what is the error you get from the "update" sub?

 

[md85]

Yeah, I think I kind of understand how it can be done from Access; thing is have created a sort of Dashboard in Excel for the front-end so it would definately be better to manage everything from the Excel side of things and leave Access as the back-end.

The error I'm getting when UPDATE is run is:

Run-time Error '-2147467259 (80004005)'

The Microsoft Jet database engine cannot open the file 'Cocuments and Settings\Alice\Desktop\Database'. It is already opened exclusively by another user or you need permission to view its data.


and the the debug comes up with the error on the line .Open stCon

 

[chergh]

I dont think your connection string is correct.

DataLocation = ThisWorkbook.Path & DataLocation

Should be

DataLocation = ThisWorkbook.Path & DBName.mdb

I think, I don't use access so much these days.

 

[md85]

hmmmm....thing is for that part I had defined

Public Const DataLocation As String = "C:\Documents and Settings\Alice\Desktop\Database\TestDatabase21.accdb"