Access to Excel

  • Thread starter Thread starter Rover
  • Start date Start date
R

Rover

I'd like to populate given cells in Excel with given fields from and
Access table. Can anyone direct me to where I can find information on
how to do that or just tell me how?

Jim
 
I'd like to populate given cells in Excel with given fields from and
Access table. Can anyone direct me to where I can find information on
how to do that or just tell me how?

Jim
Click to expand...

Look up the TransferSpreadsheet method in Access help.
Note the fact that you cannot export data to a specified RANGE of
cells in a worksheet.
Export it to a new sheet, then move it from within Excel.
 
Sub GetItFromAccess()

Application.ScreenUpdating = False

Sheets("Import").Activate

Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset

Dim xlApp As Object
' Dim xlWb As Object
Dim xlWs As Object
Dim recArray As Variant
Dim strDB As String
Dim fldCount As Integer
Dim recCount As Long
Dim iCol As Integer
Dim iRow As Integer

' Set the string to the path of the database
strDB = "H:\Payroll Stuff\My Payroll Database.mdb;" & "Jet OLEDB:Database Password=klasflkd"

' Open connection to the database
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";"

' Open recordset based on Access Payroll XYZ table
rst.Open "SELECT Social,[Unit Type], val(format(Units,""0.00""))AS Units,val(format(Wages,""0.00""))AS Wages,SubAcct AS Sub " &
_
" FROM [Prod Rpt Data] ;", cnt '& _
'"GROUP BY [Acct],SubAcct,Social,[Unit Type];"


Range("Payroll1").Activate
Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight)).Select
Selection.Cells.Clear


' Copy field names to the first row of the worksheet
fldCount = rst.Fields.Count
For iCol = 1 To fldCount
ActiveSheet.Cells(3, iCol).Value = rst.Fields(iCol - 1).Name
Next

' Copy the recordset to the worksheet, starting in cell A2
ActiveSheet.Cells(4, 1).CopyFromRecordset rst
ActiveSheet.Cells(3, 1).Activate
Names.Add Name:="Payroll1", RefersTo:=ActiveCell
' Auto-fit the column widths and row heights
Selection.CurrentRegion.Columns.AutoFit
Selection.CurrentRegion.Rows.AutoFit
Range(ActiveCell, ActiveCell.End(xlToRight)).Select

With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.MergeCells = False
End With
Selection.Font.Bold = True

Range("Payroll1").Offset(1, 2).Activate
Range(ActiveCell, ActiveCell.Offset(0, 1).End(xlDown)).Select
Selection.NumberFormat = "#,##0.00"

' Close ADO objects

rst.Close
cnt.Close

Set rst = Nothing
Set cnt = Nothing

' Cells(4, 3).Activate
' Range(ActiveCell, ActiveCell.Offset(0, 1).End(xlDown)).Select
' Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"

Cells(3, 1).Activate
Range(ActiveCell, ActiveCell.Offset(0, 4).End(xlDown)).Select
Names.Add Name:="Payroll", RefersTo:=Selection

End Sub


HTH

--
RMC,CPA


I'd like to populate given cells in Excel with given fields from and
Access table. Can anyone direct me to where I can find information on
how to do that or just tell me how?

Jim
 
Thanks... I understand the code but how do I implement it? I do VBA
coding in Access but this looks like it should go on the Excel side.
How do I get Excel to run it?

TIA

Jim
 
I am confused. I thought you said you do VBA coding. The sample code is for running the query from Excel. I thought that was what
you wanted to do. Obviously, to implement it, you would need to either add a button assigned to this macro or add some other control
in Excel where you reference this macro in one of the events for that control. Of course, you would need to change the details to
reference your database and its tables or queries, and change the SQL to get the data you want.

Are you saying you want to run this from Access instead of Excel?
--
RMC,CPA


Thanks... I understand the code but how do I implement it? I do VBA
coding in Access but this looks like it should go on the Excel side.
How do I get Excel to run it?

TIA

Jim

R. Choate said:
Sub GetItFromAccess()

Application.ScreenUpdating = False

Sheets("Import").Activate

Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset

Dim xlApp As Object
' Dim xlWb As Object
Dim xlWs As Object
Dim recArray As Variant
Dim strDB As String
Dim fldCount As Integer
Dim recCount As Long
Dim iCol As Integer
Dim iRow As Integer

' Set the string to the path of the database
strDB = "H:\Payroll Stuff\My Payroll Database.mdb;" & "Jet OLEDB:Database Password=klasflkd"

' Open connection to the database
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";"

' Open recordset based on Access Payroll XYZ table
rst.Open "SELECT Social,[Unit Type], val(format(Units,""0.00""))AS Units,val(format(Wages,""0.00""))AS Wages,SubAcct AS Sub "
&
_
" FROM [Prod Rpt Data] ;", cnt '& _
'"GROUP BY [Acct],SubAcct,Social,[Unit Type];"


Range("Payroll1").Activate
Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight)).Select
Selection.Cells.Clear


' Copy field names to the first row of the worksheet
fldCount = rst.Fields.Count
For iCol = 1 To fldCount
ActiveSheet.Cells(3, iCol).Value = rst.Fields(iCol - 1).Name
Next

' Copy the recordset to the worksheet, starting in cell A2
ActiveSheet.Cells(4, 1).CopyFromRecordset rst
ActiveSheet.Cells(3, 1).Activate
Names.Add Name:="Payroll1", RefersTo:=ActiveCell
' Auto-fit the column widths and row heights
Selection.CurrentRegion.Columns.AutoFit
Selection.CurrentRegion.Rows.AutoFit
Range(ActiveCell, ActiveCell.End(xlToRight)).Select

With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.MergeCells = False
End With
Selection.Font.Bold = True

Range("Payroll1").Offset(1, 2).Activate
Range(ActiveCell, ActiveCell.Offset(0, 1).End(xlDown)).Select
Selection.NumberFormat = "#,##0.00"

' Close ADO objects

rst.Close
cnt.Close

Set rst = Nothing
Set cnt = Nothing

' Cells(4, 3).Activate
' Range(ActiveCell, ActiveCell.Offset(0, 1).End(xlDown)).Select
' Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"

Cells(3, 1).Activate
Range(ActiveCell, ActiveCell.Offset(0, 4).End(xlDown)).Select
Names.Add Name:="Payroll", RefersTo:=Selection

End Sub


HTH
Click to expand...
 
I want to run this in Excel. I do VBA coding in Access. I've just
never done anything in Excel. I have never seen a button or control in
Excel (I live a sheltered life). If I could put a button on an Excel
spreadsheet, I could do this. I don't know where to start. Are there
any examples out there?
 
This is VERY easy. Go to TOOLS>CUSTOMIZE and choose the toolbars tab. Then choose the forms toolbar. Then just choose the button
control that you will recognize from Access. Draw your button and give it the caption you want, right on the button. As soon as you
finish drawing the button, it will ask you what macro you want to assign to the button. You would choose "This Workbook" from the
source dropdown at the bottom of the dialog, and then choose a sub to run from the list. Bada-bing-bada-boom. That's it, aside from
adjusting the ADO code to suit your situation.

HTH
--
RMC,CPA


I want to run this in Excel. I do VBA coding in Access. I've just
never done anything in Excel. I have never seen a button or control in
Excel (I live a sheltered life). If I could put a button on an Excel
spreadsheet, I could do this. I don't know where to start. Are there
any examples out there?
 
LOL....Thanks. For a non-CPA, your pretty funny.

I'm glad the solution helped you.

Richard
--
RMC,CPA


Thanks! You've opened up a whole new world for me. For a CPA, you're
an OK guy.
 
Back
Top