Running Excel from Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to send data from an Access form to an Excel file where the user can then print the Excel file and quit Excel without saving changes. However, when I run the command, I only see the Excel file flash on the screen. How can I keep focus on the Excel sheet and allow the user to print and quit Excel?

On Error GoTo Err_Command92_Click

Dim MyXL As Object

Set MyXL = CreateObject("Excel.Application")
MyXL.Visible = True
'Only XL 97 supports UserControl Property
On Error Resume Next
MyXL.UserControl = True

MyXL = GetObject("r:\comfort\LABEL.XLS")
MyXL.Parent.Windows(1).Visible = True


'The next 18 "If" statements check to see if field is empty, if not, send the data to excel
If IsNull(Me![SAMPLE NUMBER]) Then
MsgBox "MISSING SAMPLE NUMBER", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(14, 3).Value = Me![SAMPLE NUMBER]
End If

If IsNull(Me![WORK ORDER #]) Then
MsgBox "MISSING WORK ORDER", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(15, 3).Value = Me![WORK ORDER #]
End If

If IsNull(Me![PROJECT #]) Then
MsgBox "MISSING PROJECT #", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(15, 9).Value = Me![PROJECT #]
End If

If IsNull(Me![ENGINEER]) Then
MsgBox "MISSING ENGINEER", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(16, 3).Value = Me![ENGINEER]
End If

If IsNull(Me![TECHNICIAN]) Then
MsgBox "MISSING TECHNICIAN", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(16, 9).Value = Me![TECHNICIAN]
End If

If IsNull(Me![SEAT TYPE]) Then
MsgBox "MISSING SET TYPE", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(17, 3).Value = Me![SEAT TYPE]
End If

If IsNull(Me![SEATING POSITION]) Then
MsgBox "MISSING SEATING POSITION", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(18, 3).Value = Me![SEATING POSITION]
End If

If IsNull(Me![SEATING ROW]) Then
MsgBox "MISSING SEATING ROW", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(19, 3).Value = Me![SEATING ROW]
End If

If IsNull(Me![TRIM STYLE]) Then
MsgBox "MISSING TRIM STYLE", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(20, 3).Value = Me![TRIM STYLE]
End If

If IsNull(Me![ADJUSTER]) Then
MsgBox "MISSING ADJUSTER", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(21, 3).Value = Me![ADJUSTER]
End If

If IsNull(Me![LUMBAR]) Then
MsgBox "MISSING LUMBAR", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(22, 3).Value = Me![LUMBAR]
End If

If IsNull(Me![MODEL YEAR]) Then
MsgBox "MISSING MODEL YEAR", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(17, 9).Value = Me![MODEL YEAR]
End If

If IsNull(Me![OEM]) Then
MsgBox "MISSING OEM", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(18, 9).Value = Me![OEM]
End If

If IsNull(Me![PROGRAM]) Then
MsgBox "MISSING PROGRAM", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(19, 9).Value = Me![PROGRAM]
End If

If IsNull(Me![NAMEPLATE]) Then
MsgBox "MISSING NAMEPLATE", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(20, 9).Value = Me![NAMEPLATE]
End If

If IsNull(Me![SAMPLE TYPE]) Then
MsgBox "MISSING SAMPLE TYPE", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(21, 9).Value = Me![SAMPLE TYPE]
End If

If IsNull(Me![SETUP]) = Null Then
MsgBox "MISSING SETUP", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(22, 9).Value = Me![SETUP]
End If

Set MyXL = Nothing ' Release reference to the
' application and spreadsheet.


Exit_Command92_Click:
Exit Sub

Err_Command92_Click:
MsgBox Err.Description
Resume Exit_Command92_Click

End Sub
 
Set MyXL = Nothing

This kills it. Instead of this, try setting visible =
false at the beginning then where you do this, make it
visible.

-----Original Message-----
I am trying to send data from an Access form to an Excel
Click to expand...
file where the user can then print the Excel file and quit
Excel without saving changes. However, when I run the
command, I only see the Excel file flash on the screen.
How can I keep focus on the Excel sheet and allow the user
to print and quit Excel?
On Error GoTo Err_Command92_Click

Dim MyXL As Object

Set MyXL = CreateObject("Excel.Application")
MyXL.Visible = True
'Only XL 97 supports UserControl Property
On Error Resume Next
MyXL.UserControl = True

MyXL = GetObject("r:\comfort\LABEL.XLS")
MyXL.Parent.Windows(1).Visible = True


'The next 18 "If" statements check to see if field is
Click to expand...
empty, if not, send the data to excel
If IsNull(Me![SAMPLE NUMBER]) Then
MsgBox "MISSING SAMPLE NUMBER", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(14, 3).Value = Me![SAMPLE NUMBER]
End If

If IsNull(Me![WORK ORDER #]) Then
MsgBox "MISSING WORK ORDER", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(15, 3).Value = Me![WORK ORDER #]
End If

If IsNull(Me![PROJECT #]) Then
MsgBox "MISSING PROJECT #", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(15, 9).Value = Me! [PROJECT #]
End If

If IsNull(Me![ENGINEER]) Then
MsgBox "MISSING ENGINEER", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(16, 3).Value = Me! [ENGINEER]
End If

If IsNull(Me![TECHNICIAN]) Then
MsgBox "MISSING TECHNICIAN", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(16, 9).Value = Me! [TECHNICIAN]
End If

If IsNull(Me![SEAT TYPE]) Then
MsgBox "MISSING SET TYPE", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(17, 3).Value = Me![SEAT TYPE]
End If

If IsNull(Me![SEATING POSITION]) Then
MsgBox "MISSING SEATING POSITION", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(18, 3).Value = Me! [SEATING POSITION]
End If

If IsNull(Me![SEATING ROW]) Then
MsgBox "MISSING SEATING ROW", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(19, 3).Value = Me! [SEATING ROW]
End If

If IsNull(Me![TRIM STYLE]) Then
MsgBox "MISSING TRIM STYLE", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(20, 3).Value = Me![TRIM STYLE]
End If

If IsNull(Me![ADJUSTER]) Then
MsgBox "MISSING ADJUSTER", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(21, 3).Value = Me! [ADJUSTER]
End If

If IsNull(Me![LUMBAR]) Then
MsgBox "MISSING LUMBAR", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(22, 3).Value = Me![LUMBAR]
End If

If IsNull(Me![MODEL YEAR]) Then
MsgBox "MISSING MODEL YEAR", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(17, 9).Value = Me![MODEL YEAR]
End If

If IsNull(Me![OEM]) Then
MsgBox "MISSING OEM", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(18, 9).Value = Me![OEM]
End If

If IsNull(Me![PROGRAM]) Then
MsgBox "MISSING PROGRAM", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(19, 9).Value = Me! [PROGRAM]
End If

If IsNull(Me![NAMEPLATE]) Then
MsgBox "MISSING NAMEPLATE", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(20, 9).Value = Me! [NAMEPLATE]
End If

If IsNull(Me![SAMPLE TYPE]) Then
MsgBox "MISSING SAMPLE TYPE", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(21, 9).Value = Me![SAMPLE TYPE]
End If

If IsNull(Me![SETUP]) = Null Then
MsgBox "MISSING SETUP", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(22, 9).Value = Me![SETUP]
End If

Set MyXL = Nothing ' Release reference to the
' application and spreadsheet.


Exit_Command92_Click:
Exit Sub

Err_Command92_Click:
MsgBox Err.Description
Resume Exit_Command92_Click

End Sub
.
Click to expand...
 
I'm not an expert in Excel, but you can try this other way

Dim MyXL As Object

Set MyXL = GetObject("r:\comfort\LABEL.XLS")
MyXL.Parent.Visible = True
MyXL.Parent.Windows(1).Visible = True

'The next 18 "If" statements check to see if field is empty, if not, send
the data to excel
.... ....

Set MyXL = Nothing

It seems to me that if you use the CreateObject method, the variable MyXL
will contain a reference to the Excel Application, during all the process,
not to the workbook "Label.xls" (which is what you wanted to do, don't
you?). That is why some methods or properties seems to not function
properly.

HTH

--
Saludos desde Barcelona
Juan M. Afan de Ribera
<MVP Ms Access>
http://www.juanmafan.tk
http://www.clikear.com/webs4/juanmafan


Gerri said:
I am trying to send data from an Access form to an Excel file where the
Click to expand...
user can then print the Excel file and quit Excel without saving changes.
However, when I run the command, I only see the Excel file flash on the
screen. How can I keep focus on the Excel sheet and allow the user to print
and quit Excel?
On Error GoTo Err_Command92_Click

Dim MyXL As Object

Set MyXL = CreateObject("Excel.Application")
MyXL.Visible = True
'Only XL 97 supports UserControl Property
On Error Resume Next
MyXL.UserControl = True

MyXL = GetObject("r:\comfort\LABEL.XLS")
MyXL.Parent.Windows(1).Visible = True


'The next 18 "If" statements check to see if field is empty, if not, send the data to excel
If IsNull(Me![SAMPLE NUMBER]) Then
MsgBox "MISSING SAMPLE NUMBER", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(14, 3).Value = Me![SAMPLE NUMBER]
End If

If IsNull(Me![WORK ORDER #]) Then
MsgBox "MISSING WORK ORDER", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(15, 3).Value = Me![WORK ORDER #]
End If

If IsNull(Me![PROJECT #]) Then
MsgBox "MISSING PROJECT #", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(15, 9).Value = Me![PROJECT #]
End If

If IsNull(Me![ENGINEER]) Then
MsgBox "MISSING ENGINEER", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(16, 3).Value = Me![ENGINEER]
End If

If IsNull(Me![TECHNICIAN]) Then
MsgBox "MISSING TECHNICIAN", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(16, 9).Value = Me![TECHNICIAN]
End If

If IsNull(Me![SEAT TYPE]) Then
MsgBox "MISSING SET TYPE", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(17, 3).Value = Me![SEAT TYPE]
End If

If IsNull(Me![SEATING POSITION]) Then
MsgBox "MISSING SEATING POSITION", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(18, 3).Value = Me![SEATING POSITION]
End If

If IsNull(Me![SEATING ROW]) Then
MsgBox "MISSING SEATING ROW", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(19, 3).Value = Me![SEATING ROW]
End If

If IsNull(Me![TRIM STYLE]) Then
MsgBox "MISSING TRIM STYLE", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(20, 3).Value = Me![TRIM STYLE]
End If

If IsNull(Me![ADJUSTER]) Then
MsgBox "MISSING ADJUSTER", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(21, 3).Value = Me![ADJUSTER]
End If

If IsNull(Me![LUMBAR]) Then
MsgBox "MISSING LUMBAR", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(22, 3).Value = Me![LUMBAR]
End If

If IsNull(Me![MODEL YEAR]) Then
MsgBox "MISSING MODEL YEAR", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(17, 9).Value = Me![MODEL YEAR]
End If

If IsNull(Me![OEM]) Then
MsgBox "MISSING OEM", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(18, 9).Value = Me![OEM]
End If

If IsNull(Me![PROGRAM]) Then
MsgBox "MISSING PROGRAM", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(19, 9).Value = Me![PROGRAM]
End If

If IsNull(Me![NAMEPLATE]) Then
MsgBox "MISSING NAMEPLATE", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(20, 9).Value = Me![NAMEPLATE]
End If

If IsNull(Me![SAMPLE TYPE]) Then
MsgBox "MISSING SAMPLE TYPE", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(21, 9).Value = Me![SAMPLE TYPE]
End If

If IsNull(Me![SETUP]) = Null Then
MsgBox "MISSING SETUP", vbOKOnly
Exit Sub
Else
MyXL.Application.Cells(22, 9).Value = Me![SETUP]
End If

Set MyXL = Nothing ' Release reference to the
' application and spreadsheet.


Exit_Command92_Click:
Exit Sub

Err_Command92_Click:
MsgBox Err.Description
Resume Exit_Command92_Click

End Sub
Click to expand...
 
Back
Top