Updating Excel cells

[Guest]

I am using the following code to open or copy an excel spreadsheet within an
Access form:

Dim xl As Object
Dim wkbName As String
Dim tplName As String
wkbName = "C:\Innatech\Dynamic Database\Checklists\" & Me.JobNo &
".xls" '1
If Dir(wkbName) = "" Then
tplName = "C:\Innatech\In Progress\Dynamic Final Assembly
Checklist.xls"
FileCopy tplName, wkbName
End If
Set xl = CreateObject("Excel.application")
xl.workbooks.Open wkbName
xl.Visible = True

Is it possible to fill in certain cells on the spreadsheet from fields in
the database?

Thanks for your help!
Cindy

 

[Guest]

Cindy, you can do anything in Excel from Access that you can do in Excel.
Here is a brief example. You can substitute you field name for what you see
here. If you want to put in a record set rather than do each by hand see the
example below. You can also do whatever formatting you need. Once you
understand this, go into the Oject Browser in VBA editor and explore the
possibilites

Dim xlApp As Excel.Application 'Application Object
Dim xlBook As Excel.Workbook 'Workbook Object
Dim xlSheet As Excel.Worksheet 'Worksheet Object

Set xlSheet = xlBook.ActiveSheet
With xlSheet
.Cells(1, 1) = "ITM"
.Cells(1, 2) = Me.txtCurrYear & _
" Activity # Description"
.Cells(1, 3) = "Budget " & Me.txtCurrYear
.Cells(1, 4).Value = Me.txtCurrYear & " YTD Budget"
.Cells(1, 5) = "Actuals YTD"
.Cells(1, 6) = "Variance YTD"
.Cells(1, 7) = "TO GO"
.Cells(1, 8) = IIf(Me.cboPeriod >= 1, "JAN ACT", "JAN ETC")
.Cells(1, 9) = IIf(Me.cboPeriod >= 2, "FEB ACT", "FEB ETC")
End With

' Copy a record set
Set qdf = CurrentDb.QueryDefs("qselSCCBrpt")
qdf.Parameters(0) = Me.cboResource
qdf.Parameters(1) = Me.cboPeriod
Set rstSCCB = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
xlSheet.Cells(intX + 3, 1).CopyFromRecordset rstSCCB

 

[Guest]

Hi! Just FYI -- I've used a limited amount of VB code in Access with help
from the Microsoft community. I've only made minor revisions to VB code in
Excel after I've created it by recording a Macro ... and again, usually with
help from the Microsoft community.

I haven't been able to get the code you suggested to work in Access ... I
get the error message "User-defined type not defined" on the Dim xlApp line.
Is this VB code supposed to go in Excel rather than Access?

When I put the code (with my Access field names inserted) into my worksheet,
I get the error message "Invalid use of Me keyword". Here's the code I
used....

Sub testing()

Dim xlApp As Excel.Application 'Application Object
Dim xlBook As Excel.Workbook 'Workbook Object
Dim xlSheet As Excel.Worksheet 'Worksheet Object

Set xlSheet = xlBook.ActiveSheet
With xlSheet
.Cells(3, 2) = Me.JobID
End With

End Sub

Can you tell what I'm doing wrong? (P.S. Did I get the ".Cells" right for
cell B3?)

Thanks for your help!
Cindy

 

[Guest]

Check your references to see if you have Microsoft Outlook 9.0 Object Library
selected. Also you may need Microsoft Office 11.0 Object Library.