VBA Can't open available Excel file

In the following vba code I am getting "object variable or with block variable not set" error
marked by the *
"file_to_use" is being passed in from a file dialogue.
The file itself is available as I can open it outside of the app.
Can you spot anything I am missing?

Currently my only fix is to restart the application. So it works the first time only in other words.

Dim curRange(5)
Dim iCounter As Integer
Dim strReceipt_list As String
Dim removeChar As Integer
Dim xl As Excel.Application
Dim wkbk As Excel.Workbook
Dim wk As Excel.Worksheet

On Error GoTo errHandler
 
Set xl = CreateObject("Excel.Application")
Set wkbk = xl.Workbooks.Open(file_to_use)
Set wk = wkbk.Sheets(1)
   
strReceipt_list = "("

For iCounter = 0 To 4
*   curRange(iCounter) = ActiveCell.Offset(iCounter, 0).Value
Next iCounter

For iCounter = 0 To 4
   strReceipt_list = strReceipt_list + CStr((curRange(iCounter))) + ","
Next iCounter
   
strReceipt_list = strReceipt_list + "*"
strReceipt_list = Replace(strReceipt_list, ",*", ")")
   
wkbk.Close
xl.Quit
Set wk = Nothing
Set wkbk = Nothing
Set xl = Nothing
   
Exit Sub

errHandler:

   wkbk.Close
   xl.Quit
   Set wk = Nothing
   Set wkbk = Nothing
   Set xl = Nothing
claghornAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DrTribosCommented:
You might have XL open already?

Try:
On Error Resume Next
Set xlapp = GetObject(, "Excel.Application")
If Err Then
Set xl = CreateObject("Excel.Application")

Also, I ...
Dim xlapp As Object
Dim xlbook As Object
Dim xlsheet As Object

But not sure if that would make the difference.

HTH
0
claghornAuthor Commented:
The problem was the "xl.Quit" line. I do not know why. Yes Excel is opened by my procedure code and I thought I was closing it too. It seems to work over and over now w/o having to restart and in some cases re-start the computer to release the files that get locked.

I am struggling with managing the files that this app opens and closes.
Do you know how to properly manage this. ie: I have a word mail merge vba app that opens closes many files. How can I open and close them while the app runs so the person can open these files and have a look at them w/o quitting the app?

I know there is a document object. It has an index. ie: document(1), document(2) etc..
I seem to have no control over these. If I have a template open I want to use it to create my "save as" copy with the merged data in it then I want to close the template. But I end up closing everything including the very app itself which is embedded in a mail merge document.

I'm using statements like this:

Documents.Open FileName:=FileName etc..
ActiveDocument.MailMerge.OpenDataSource Name:=

ActiveDocument.MailMerge.DataSource.Close
0
DrTribosCommented:
I have similar code, though admit that I've only modified and studied it.  I did find that when I used it in my New_Document sub that it would routinely crash.  I removed it from there and can call it 50 or so times in a heartbeat without fail.  Open and close every time.

Here is how my files are closed:
If bstartapp = True Then
    xlbook.Save
    xlbook.Close
    xlapp.Quit
End If

Set xlapp = Nothing
Set xlbook = Nothing
Set xlsheet = Nothing

End Sub

Open in new window


Note that bstartapp is a booleen that is set to true if the app was not already open.

For debugging purposes I also put in a counter to tell me how many sheets were open, it yielded some surprising results.  The counter would return a +ve number (21 to be precise) which would be determined from debug.print and the hit a Stop line.

If I then typed Set xl = CreateObject("Excel.Application") into the immediate window everything would correct itself... it was unituitive.  In the end my frustrations seemed to be related to word starting up a new document and there not being sufficient opportunity to pass control back to the OS.  I tried a sprinkling of DoEvents but that did not help.  Eventually, I removed the call to this sub from the startup and found another way to call it.

HTH
0
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

DrTribosCommented:
This bit might help...  try a loop to scroll through each of the open objects in your collection and then assign...  

For i = 1 To xlapp.workbooks.count
    If xlapp.workbooks(i).Name = book & ".xlsx" Then
        bbookopen = True

        Exit For
    Else
        bbookopen = False
        Stop
    End If
Next i

If bbookopen = False Then
    Set xlbook = xlapp.workbooks.Open(ThisDocument.Path & "\" & book & ".xlsx")
Else
    Set xlbook = xlapp.workbooks(i)
End If

Set xlsheet = xlbook.sheets(sheet)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
claghornAuthor Commented:
Is it true that any file that the app opens and closes during run time cannot be accessed outside of the app till the app shuts down?
0
DrTribosCommented:
I don't think that is a true statement.  

I just opened my XL file (using VBA late binding) extracted some info and inserted into word. Left word open with a bunch of VBA still running and then manually opened XL without issue.
0
claghornAuthor Commented:
last question:

I have the following: it uses a word mail merge template and saves a copy that has the data from the source excel file. I save it and close in the code. I leave the app running. But this copy is locked by the app still. The source file is released. The template is released. The resulting document is not released. Any resulting document is not released till the app closes. Can I release resulting docs while the app is running?

What you may need to know is that the app is running in behind a mail merge doc itself. It never gets changed its just used as the template for a mail merge.

ChangeFileOpenDirectory UserForm1.txtPathToMergedDocs.Text
    ActiveDocument.SaveAs2 FileName:=FileToSave, _
        FileFormat:=wdFormatXMLDocument, LockComments:=False, Password:="", _
        AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
        EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
        :=False, SaveAsAOCELetter:=False, CompatibilityMode:=14
    ActiveWindow.ActivePane.VerticalPercentScrolled = 0
           
    ActiveDocument.Close (False) 'should close the newly created file. yes it does.
    Documents(FileName).Close (False) 'should close the template used. YES it does.
0
DrTribosCommented:
Just a guess but perhaps you need to set the document to nothing in order to release?
0
claghornAuthor Commented:
In your project do you have your code in behind a mail merge document? Or is it in a word doc that just has a form built that employs/manipulates other Word docs only.
How do you make your Word VBA form available to your user?
That code I showed you in the last post was taken via the macro recorder so I don't think "set object to nothing" would be recognized.

For me the data source file, excel is fine. Its the docs that get created from it that are locked while the app runs. I'll take one last crack at it before I close this issue out with you.

Thanks again for your help.
0
DrTribosCommented:
My code is not part of a mail merge as such. I use it to write constants to vba - like a configuration file. User would not be aware that it does anything. Basically I am only working with 1 doc at a time.  The xl file data is associated with the document template. I can open and close documents without any issues.
0
DrTribosCommented:
I am on my phone so struggle to get an example showing how to set something to nothing.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Word

From novice to tech pro — start learning today.