mikeprof99
New member
- Local time
- Today, 16:42
- Joined
- May 18, 2010
- Messages
- 4
Ok guys this is what I need to do. I have an Access application that does lot of processing and enhrichment of some data. At the end of my process it will derive some values that I will need to transfer it to Excel Template I have designed. Below is the layout of Excel File
UniqueID Region1 Region2 Region3 Region4
1001 (Loc CellD12) Loc cell E12 Loc Cell F12 Loc Cell G12
1002 (Loc CellD13) Loc Cell E13
1003
1004
1006
Note - The Loc Cell D12 means the actual cell D12 where I will have to populate the values.
My Access Table (tblRegionSalesCalc) is Designed as
UniqueID CellLoc Values
1001 D12 2365
1001 E12 9892
1001 F12 4523
1002 E13 2325
The name of my Excel workbook is SalesByIDRegion and the name of my worksheet is RegionalSales
How do I go about writing a VBA code that at a click of a button will take the data from the Access table (tblRegionSalesCalc) open the Excel workbook SalesByIDRegion and use the worksheet RegionalSales and take the CellLoc and its Values from the Access table and put the associated values in the respective cell. Any help you can provide will be greatly appreciated. So the value of cell D12 in the table is 2365, I want the same value to be populated in the cell D12.
I found a code on the internet written by Ashish and is pasted below. Now I need some help in modifying the code to point it the destination cell as specified in my Access table, grab the data from the table and use the workbook and worksheet I have.
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As Database
Dim rs As Recordset
Dim intLastCol As Integer
Const conMAX_ROWS = 20000
Const conSHT_NAME = "SomeSheet"
Const conWKB_NAME = "C:\Documents and Settings\thakkami\Desktop\book1.xls"
Set db = CurrentDb
Set objXL = New Excel.Application
Set rs = db.OpenRecordset("tblExcelValuesTransport", dbOpenSnapshot)
With objXL
.Visible = True
Set objWkb = .Workbooks.Open(conWKB_NAME)
On Error Resume Next
Set objSht = objWkb.Worksheets(conSHT_NAME)
If Not Err.Number = 0 Then
Set objSht = objWkb.Worksheets.Add
objSht.Name = conSHT_NAME
End If
Err.Clear
On Error GoTo 0
intLastCol = objSht.UsedRange.Columns.Count
With objSht
.Range(.Cells(1, 1), .Cells(conMAX_ROWS, _
intLastCol)).ClearContents
.Range(.Cells(1, 1), _
.Cells(1, rs.Fields.Count)).Font.Bold = True
.Range("A2").CopyFromRecordset rs
End With
End With
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
Set rs = Nothing
Set db = Nothing
UniqueID Region1 Region2 Region3 Region4
1001 (Loc CellD12) Loc cell E12 Loc Cell F12 Loc Cell G12
1002 (Loc CellD13) Loc Cell E13
1003
1004
1006
Note - The Loc Cell D12 means the actual cell D12 where I will have to populate the values.
My Access Table (tblRegionSalesCalc) is Designed as
UniqueID CellLoc Values
1001 D12 2365
1001 E12 9892
1001 F12 4523
1002 E13 2325
The name of my Excel workbook is SalesByIDRegion and the name of my worksheet is RegionalSales
How do I go about writing a VBA code that at a click of a button will take the data from the Access table (tblRegionSalesCalc) open the Excel workbook SalesByIDRegion and use the worksheet RegionalSales and take the CellLoc and its Values from the Access table and put the associated values in the respective cell. Any help you can provide will be greatly appreciated. So the value of cell D12 in the table is 2365, I want the same value to be populated in the cell D12.
I found a code on the internet written by Ashish and is pasted below. Now I need some help in modifying the code to point it the destination cell as specified in my Access table, grab the data from the table and use the workbook and worksheet I have.
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As Database
Dim rs As Recordset
Dim intLastCol As Integer
Const conMAX_ROWS = 20000
Const conSHT_NAME = "SomeSheet"
Const conWKB_NAME = "C:\Documents and Settings\thakkami\Desktop\book1.xls"
Set db = CurrentDb
Set objXL = New Excel.Application
Set rs = db.OpenRecordset("tblExcelValuesTransport", dbOpenSnapshot)
With objXL
.Visible = True
Set objWkb = .Workbooks.Open(conWKB_NAME)
On Error Resume Next
Set objSht = objWkb.Worksheets(conSHT_NAME)
If Not Err.Number = 0 Then
Set objSht = objWkb.Worksheets.Add
objSht.Name = conSHT_NAME
End If
Err.Clear
On Error GoTo 0
intLastCol = objSht.UsedRange.Columns.Count
With objSht
.Range(.Cells(1, 1), .Cells(conMAX_ROWS, _
intLastCol)).ClearContents
.Range(.Cells(1, 1), _
.Cells(1, rs.Fields.Count)).Font.Bold = True
.Range("A2").CopyFromRecordset rs
End With
End With
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
Set rs = Nothing
Set db = Nothing