This is the problem. This range name assigning should be
done without opening this worksheet. I'm trying to do this
by setting up a referense to this spreadsheet.
When I open this spreadsheet there is no this range I want
to have or it's corrupted - just empty Excel frame, which
caused I think by Set MyXL = Nothing.
I'm using the code below and everything is working good.
Please, look at that. But, by using this code I'm making
this spreadsheet visible for a user for a little while,
which I wouldn't like to do.
The code, which is working, but with a short file
apperance:
Dim ref As Reference
Dim MyXL As Object ' Variable to hold reference
' to Microsoft Excel.
Dim ExcelWasNotRunning As Boolean ' Flag for final
release.
Dim Msg
' Test to see if there is a copy of Microsoft Excel
already running.
On Error Resume Next ' Defer error trapping.
' Getobject function called without the first argument
returns a
' reference to an instance of the application. If the
application isn't
' running, an error occurs. Note the comma used as the
first argument
' placeholder.
Set ref = References!Excel
Set MyXL = GetObject(, "Excel.Application")
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear ' Clear Err object in case error occurred.
' Check for Excel. If Excel is running,
' enter it into the Running Object table.
DetectExcel
DoCmd.OutputTo acOutputQuery, _
"Ingredient Specs",
acFormatXLS, "MyFile.xls"
' Set the object variable to reference the file you want
to see.
Set MyXL = GetObject
("MyFile.xls")
' Show Microsoft Excel through its Application property.
Then
' show the actual window containing the file using the
Windows
' collection of the MyXL object reference.
MyXL.Application.Visible = True
MyXL.Parent.Windows(1).Visible = True
' Assign a range name
MyXL.Application.Worksheets("MyFile").Range
("A1:H400").Name = "d"
' If this copy of Microsoft Excel was not already running
when you
' started, close it using the Application property's Quit
method.
' Note that when you try to quit Microsoft Excel, the
Microsoft Excel
' title bar blinks and Microsoft Excel displays a message
asking if you
' want to save any loaded files.
'To avoid it I'm using this:
If ExcelWasNotRunning = True Then
MyXL.Save
MyXL.Application.Quit
Else
MyXL.Save
MyXL.Application.Quit
End If
Set MyXL = Nothing ' Release reference to the
' application and spreadsheet.
Msg = MsgBox(".....", _
vbExclamation, "Recipes Price Update")
Item_Price_exit:
Exit Function
Item_Price_Err:
MsgBox Error
Resume Item_Price_exit
End Function
Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim hWnd As Long
' If Excel is running this API call returns its handle.
hWnd = FindWindow("XLMAIN", 0)
If hWnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage hWnd, WM_USER + 18, 0, 0
End If
End Sub
<<<This is in a module:>>>
Option Compare Database
' Declare necessary API routines:
Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As Long) As Long
Declare Function SendMessage Lib "user32" Alias _
"SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long
