open excel file from vba, need help debugging code 1

[newprogamer]

Hello Everyone, I have written code to open an excel file or either a word file depending on the option button the user selects. This code will open the word file. But, the excel file will not open. The objExcelfile always equals nothing. Any help would be appreciated.

'**************


Private Sub OpenQuote()

On Error Resume Next
Dim strFile As String 'The formal word document or the excel spreadsheets

'open using with VBA
If optWordQuote.Value = True Then 'Open Word Quote
Dim objWordapp As Object 'this is Word
Dim objWordfile As Object 'this is the WordQuote
strFile = DataGrid1.Columns("WordQuote") 'full path to WordQuote

Set objWordapp = CreateObject("Word.Application")

If objWordapp Is Nothing Then '*******If Word or word file are not there close
MsgBox "Microsoft Word is not found!", vbInformation, "ERROR"
GoTo quickEnd

Else
Set objWordfile = objWordapp.documents.Open(strFile) 'open quote
objWordapp.Visible = True
Set objWordfile = Nothing 'this will close the file
Set objWordapp = Nothing
End If


Else
'open excel spreadsheet
Dim objExcelapp As Object 'this is Excel
Dim objExcelfile As Object 'this is the Excel file
strFile = DataGrid1.Columns("Name") 'full path to Excel file

Set objExcelapp = CreateObject("Excel.Application")

If objExcelapp Is Nothing Then '*******If excel or excel file are not there close
MsgBox "Microsoft Excel is not found!", vbInformation, "ERROR"
GoTo quickEnd

Else
Set objExcelfile = objExcelapp.Workbook.Open(strFile) 'open spreadsheet
objExcelapp.Visible = True
Set objExcelfile = Nothing 'this will close the file
Set objExcelapp = Nothing
End If

End If

quickEnd:
Set objWordfile = Nothing 'this will close the file
Set objWordapp = Nothing
Set objExcelfile = Nothing
Set objExcelapp = Nothing

End Sub

 

[fumei]

First:

[code}Set objWordfile = objWordapp.documents.Open(strFile) 'open quote
objWordapp.Visible = True
Set objWordfile = Nothing 'this will close the file
Set objWordapp = Nothing
[/code]

In this code "Set objWordfile = Nothing" does NOT close the file. Are you saying it does? It should not.

Second:

Set objExcelfile = objExcelapp.Workbook.Open(strFile)

should be Workbooks - not Workbook.

Also, I am assuming this is demonstartion purposes, as the code opened the files and does nothing at all.

Gerry

 

[newprogamer]

Thanks fumei,

That was the problem. I needed a "s".

The program is only going to save information from quotes into a record in a database. Then, the user will be able to search using a datagrid based on different fields. Once they click on a row, this procedure will retrieve the excel file or word document from access.

"Nothing", only makes the document or spreadsheet blank right?

 

[fumei]

Nothing destroys the object. Actually, I think it would be more accurate to say it destroys the memory address.

Have you run this?

Creating the object and opening the file, then destroying the object - at least in my attempts - leaves the file open.

Usually you would:

create application object
open file - and you do not need a file object to do this
do what ever it is you are doing
save the file - if that is what you need
close the file
quit the app (eg objWordApp.Quit)
THEN destroy the object - Set objWordApp = Nothing

Gerry

 

[newprogamer]

Hello, Thanks again for your help!

I'm not sure how to open without the object file.
Set objWordfile = objWordapp.documents.Open(strFile)
Set objExcelapp = CreateObject("Excel.Application")
Do you have a suggestion on how to open the file without a file object?

I have run the code and I do end up with problems like file open, open for read only, and MS Excel is waiting for another application to complete an OLE action. I looked at your suggestions for closing the application. Will this work?

I have changed my code to
quickEnd:
objWordfile.Close
Set objWordfile = Nothing
objWordapp.Quit
Set objWordapp = Nothing

objExcelfile.Close
Set objExcelfile = Nothing
objExcelapp.Quit
Set objExcelapp = Nothing

 

[newprogamer]

Thanks Fumei,
I have changed my code to add the close and quit in the other procedures where I open word and excel. I do not receive the errors listed above any longer. Thanks again.

 

[fumei]

Once you have created the instance of the application, then you can use the application to open a file. There is a long standing pro/con regarding early vs late binding. However, since you are using CreateObject("Excel.Application"), I would change your code from:

Dim objWordapp As Object

to

Dim objWordapp As Word.Application
Set objWordapp = CreateObject("Word.Application")

then you can simply open a file.

objWordapp.Documents.Open filename:=strFile

for the Excel:

Dim objExcelapp As Excel.Application
Set objExcelapp = CreateObject("Excel.Application")

then open a file;

objExcelapp.Workbooks.Open FileName:=strFile

You do not need to create a file object. You will need to have reference set for both Word and Excel.

Also, you should not have the Set objWordapp = Nothing instructions where you have them. Follow the logic through.

You have a logic condition:

If objWordapp Is Nothing Then 
   MsgBox "Microsoft Word is not found!", vbInformation, "ERROR"
        GoTo quickEnd
Else
   '   do the file open thing
End If

Quick end makes objWordapp = Nothing.

Look at this again.

IF objWordApp Is Nothing THEN objWordapp = Nothing

Uh......the code will run, but it does not make sense. If it is nothing ..... then make it nothing??? If you mean to exit, then use Exit Sub.

Try using Step Through - press F8 in the VBE. You will notice that the code steps through setting your app objects to nothing twice. Again the code will run, but there is no point to setting something to nothing twice, and definitely no point to setting something to nothing, when it already IS nothing.



Gerry

 

[newprogamer]

Thanks a lot Gerry! Everything you said makes perfect since. Quick end should be an Exit Sub. I will make the changes you suggested.

 

[newprogamer]

I have made the changes in my code that you suggested; however, I receive error messages when the program tries to open the word document and excel spreadsheet.

   Dim strFile As String
    strFile = "C:\Quotes\HingeR4.dot"
     
     Dim objWordapp As Word.Application
     Set objWordapp = CreateObject("Word.Application")
    
[b]objWordapp.documents.Open Filename:=strFile[/b]

The error message is "HingeR4.dot is locked for editing by user". and then I also receive this error message "Microsoft Office Excel is waiting for another application to complete an OLE action."

Is there something I should do before attempting to open the file? Please help. Thanks in advance!

 

[newprogamer]

OK, my problem was that I am trying to open a template. It works fine when I change the strFile .doc.