Avatar of claghorn
claghorn asked on

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
Microsoft Word

Avatar of undefined
Last Comment
DrTribos

8/22/2022 - Mon
DrTribos

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
ASKER
claghorn

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
DrTribos

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
DrTribos

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
claghorn

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?
DrTribos

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.
ASKER
claghorn

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
DrTribos

Just a guess but perhaps you need to set the document to nothing in order to release?
ASKER
claghorn

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.
DrTribos

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
DrTribos

I am on my phone so struggle to get an example showing how to set something to nothing.