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:
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.
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:
Code:
[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]
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.