Help with access to excel

  • Thread starter Thread starter mbynoe via AccessMonster.com
  • Start date Start date
M

mbynoe via AccessMonster.com

I am trying to copy 2 totals into an Excel cell...but only one will copy when
I click the button:

Private Sub Command5_Click()
MsgBox "This will generate a Weekly Storage EIA-912 report."
'Declare a variable named MySheetPath as String
Dim MySheetPath As String

'Path and Filename
MySheetPath = "C:\Documents and Settings\mxb\My Documents"
MySheetPath = MySheetPath + "\Producing Region.xls"

'Set up object variables to refer to Excel and objects
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet

'Open an instance of Excel , open the workbook
Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(MySheetPath)

'Make sure everything is visible on the screen
Xl.Visible = True
XlBook.Windows(1).Visible = True

'Define the topmost sheet in the Workbook as XLSheet
Set XlSheet = XlBook.Worksheets(1)

On Error Resume Next
'Copy Total to FromAcces cell in the sheet.
If Me.Grouping = 1 Then
XlSheet.Range("FromAcces").Locked = False
XlSheet.Range("FromAcces") = SumOfwork_mcf
Else
If Me.Grouping = 2 Then
'Copy Producing Total to Producing cell in the sheet.
XlSheet.Range("ProducingRegion").Locked = False
XlSheet.Range("ProducingRegion") = SumOfwork_mcf
End If
End If

'Boldface the new value (optional).
XlSheet.Range("FromAcces").Font.Bold = True
XlSheet.Range("ProducingRegion").Bold = True

'Save the sheet with the new value (optional)
XlBook.Save

'Close the Access form (optional)
DoCmd.Close afForm, "Excelexport", acSaveNo



'Clean up and end with worksheet visible on the screen.
Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing

End Sub
 
Try commented out the statement "On Error Resume Next" and see if any line
of code errors out when you run the code ...

Check the Range name also ... "FromAcces" looks a bit strange. Do you mean
"FromAccess" ...

BTW, I haven't looked carefully but I don't think your code actually uses
the "Xl" (Application) object ...
 
Thank you Van....when I commented out the "On Error Resume Next", I get an
error message...on this line " XlSheet.Range("FromAcces").Locked = False"
(Method, "Range of Object _Worksheet failed"

Try commented out the statement "On Error Resume Next" and see if any line
of code errors out when you run the code ...

Check the Range name also ... "FromAcces" looks a bit strange. Do you mean
"FromAccess" ...

BTW, I haven't looked carefully but I don't think your code actually uses
the "Xl" (Application) object ...
I am trying to copy 2 totals into an Excel cell...but only one will copy
when
Click to expand...
[quoted text clipped - 54 lines]
End Sub
Click to expand...
Click to expand...
 
The syntax looks correct, AFAIK.

Do you have a *pre-defined* range "FromAcces" in Worksheets(1)?
 
The syntax works fine when I tested it (again) in A2003.

"FromAcces" is a bit strange in this context. Did you mean "FromAccess"?

Have you checked the name of the Range and made sure it is correct?
 
Back
Top