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

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

claghorn

ASKER

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

ASKER CERTIFIED SOLUTION

DrTribos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

claghorn

ASKER

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.

claghorn

ASKER

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.

DrTribos

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

claghorn

ASKER

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.

DrTribos

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