Unexplained errors...any ideas ?

[shimuno]

Hello

I've been working on a macro enabling a user to update a Word document linked to an Excel workbook. The macro open the Excel file if it closed, then the user can modify the Excel file before updating the Word document.

The code below works quite well approximately 9 times out of 10. Unfortunately, sometimes the following errors happen :
- the Excel file does not open
- the Excel file opens but it is impossible to update the fields
- the Excel file window is not activated, so the user thinks that it cannot open it (actually it is opened, but hidden behind the Word window).

Any ideas ?

Here is the code :


Sub Updatelinks


Dim xlApp As Object
Dim xlWB As Object

Set xlApp = CreateObject("Excel.Application")

On Error Resume Next

Set xlApp = GetObject(, "Excel.Application")


Set xlWB = xlApp.Workbooks("Champs automatiques.xls")

On Error GoTo 0


'Open the Excel file if it is closed

If xlWB Is Nothing Then

xlApp.AskToUpdateLinks = False

Set xlWB = xlApp.Workbooks.Open("H:\OBLIGS\Breve\Modeles\Champs automatiques.xls", ReadOnly:=True)

xlApp.Visible = True

End If

ActiveDocument.Fields.Update

End sub

Thank you very much for your help.

[GTO]

Greetings Shimuno,

I didn't include the field updating (missed seeing it), but elsewise, believe this should work for you. In short, you were creating an instance before checking to see if an instance of Excel was already running...

As it appears you are using late-binding, I wrote as such.

Hope this helps,

Mark

Sub GoGetMyExcelWB_2()
Dim objExcel As Object, _
wbWorkbook As Object, _
strPath As String, _
strWB_Name As String, _
bolExcelWasCreated As Boolean, _
bolWB_IsOpen As Boolean
'// Just to specify //
On Error GoTo 0
'// I just made a workbook in the same file as the Word file. For you, //
'// specify the path "H:\OBLIGS\Breve\Modeles\" //
strPath = ThisDocument.Path & Application.PathSeparator
'// I mis-spelled it, but here is where to put //
'// filename: "Champs automatiques.xls" //
strWB_Name = "Champs Automatics.xls"
'// Ensure no existing err.number exists //
Err.Clear
'// Change error handling to handle inline. //
On Error Resume Next
'// Here is where I believe you ran into occassional 'issues'. Let's check //
'// to see if Excel already is running first. //
Line28:
Set objExcel = GetObject(, "Excel.Application")
'// If Excel was not running, we just raised an error, so we'll use this to //
'// tell us we need to create Excel. //
If Err.Number > 0 Then
'// Clear the error //
Err.Clear
'// Create Excel, and make it visible. //
Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Visible = True
'// Since Excel wasn't running, we know we have to open the wb. //
objExcel.Application.Workbooks.Open strPath & strWB_Name
'// Reset error handling, explicitly release the object from memory, //
'// and exit the sub. //
On Error GoTo 0
Set objExcel = Nothing
Exit Sub
End If
'// Now if we made it here, we found that Excel already was running, so //
'// we'll reset error handling... //
On Error GoTo 0
'// ...and make sure the app is visible. //
objExcel.Application.Visible = True
'// Now we'll go thru and see if the workbook is already opened. //
For Each wbWorkbook In objExcel.Application.Workbooks
If wbWorkbook.Name = strWB_Name Then
'// If "Champs Automatics.xls" is already opened, note this and //
'// exit the loop. //
bolWB_IsOpen = True
Exit For
End If
Next wbWorkbook
'// If it wasn't open... //
If Not bolWB_IsOpen Then
'// ...open it and set XL's window state //
objExcel.Application.Workbooks.Open strPath & strWB_Name
objExcel.Application.WindowState = -4137
Else
'// If it was already open, increase window state to maximized. //
objExcel.Application.WindowState = -4137
End If
'// The other thing you could do (you mentioned the workbook sometimes //
'// being open, but the user being unaware as it was 'hidden') is to //
'// reduce Word to an icon. //
Application.WindowState = wdWindowStateMinimize

'// Explicitly release object. //
Set objExcel = Nothing
End Sub

[shimuno]

Wow, thank you for your help GTO. A little disclaimer here : I am not very proficient in VBA (and also : I am french...) so your explanations were very usefull !

I should have mentioned first that this Excel file (Champs automatiques.xls) is used by several user at the same time, so it has to be opened in a readonly mode. So I wrote this :


objExcel.Application.Workbooks.Open strPath & strWB_Name, ReadOnly:=True




But I have some problems to update the fields of the Word document. The macro is supposed to be linked to a button : the user click on this button if he wants to open the Excel file (if it is closed) and then click again on the button to update the Word document.


I tried many things, but I always got a message box saying that the workbook Champs automatiques is already opened and that if I open it again, all the modifications will be lost. If I clicked on the "No" button, I would get the execution error number 1004 saying (I translates from the French) that the Open method of the Workbooks class has failed.

Any ideas ?


Thank you again for your help.

[GTO]

Wow, thank you for your help GTO. A little disclaimer here : I am not very proficient in VBA (and also : I am french...) so your explanations were very usefull !


Howdy (a local 'cowboy' "Hello") Shimuno,

Nice to 'meet' you :-)

I think you are doing fine vba-wise. As to file-sharing, not a specialty of mine, but looks like you have that covered.

By the sounds of it, the "one button for two purposes" shouldn't kill us, but I am up WAY too late to tackle this tonight.

How about you show me an example file (non-proprietory of course) of both the Word file and the Excel Workbook, and I'll see if I can help. If the files are overly large or too hard to re-create, try and explain the 'updating' more thoroughly, and I'll still try.

Actually - since its been a while since I worked in Word, I'm inviting Demosthine (Scott) in for any suggested solutions as well.

Have a great day,

Mark

[shimuno]

Okay. I will send you that ASAP

(I know that this email is not very interesting, but I understand that I need to post something five times in order to send an email...so here is my fifth post).

Edit : okay, so it seems that it is not possible to join files with the forum email service. Could you please give me an email adress ?

[Demosthine]

Good Afternoon.

As GTO mentioned, he invited me along for the ride on this beautiful October day. Ok, well it's an absolutely beautiful day here in Phoenix and I hope it is over there, too.

First, I've sent you a PM with my EMail Address. If you would send me those two files that Mark mentioned, that would be wonderful.

Now, a couple questions I have...

You say that the User starts in Word and clicks a Button. This will open or activate Excel, allowing the User to make changes to the Workbook. Then, when the User clicks the Button again, it updates all of those changes in Word. Is that right?


Second, you say that the Workbook is accessed by multiple Users at once, therefore it has to be opened in the Read-Only state. Although this is not entirely true for having to open it as Read-Only (I'll get to that in a moment), why would the user need to be updating the Workbook if they can not save the changes? This doesn't make sense to me.

Regarding opening it as Read-Only... If the User needs to actually save the Workbook before returning to Word, you should look into setting the Workbook as "Shared with Tracking Changes." This will allow multiple Users to access it and save their data when they are done. There are warnings I'll give you if you choose to go that way.


Now, for having your Button available at before and after opening Excel... If you put the Button on a small Form, you can set the Form to be Modeless and "Always On Top." That will ensure the User can see this Form and Button even with Excel activated and select the Button once they are complete with the updates.

I hope this helps for now. And once I get more information, I can provide you with a better answer.

Scott

[GTO]

Hey Shimuno,:hi:

Given that its the weekend, you may read both Scott's post and mine at the same time. If that is the case, I am wondering about by your statement as to:


Edit : okay, so it seems that it is not possible to join files with the forum email service. Could you...?

You should be able to post the workbook and the word file. If you look below the "Quick Reply" box, you'll see a button "Go Advanced" - - - click it.

Now, you'll see that the next seperator bar below the Reply to Thread section, is titled "Additional Options". Expand it with the button at the right end of the green bar (it is probably already expanded) and click the "Manage Attachments" button.

A new window will pop-up. Browse button, find the file, <OK> button, then click the Upload button. Presto! You have just uploaded your first posted file :-)

I would mention that if you can zip the file and the workbook into one zip folder, you can do this in one post. If your PC doesn't have this, just post one and then the other...

If this gives you any problems, there are terrific folks running this site who can help.

Mark

[shimuno]

You say that the User starts in Word and clicks a Button. This will open or activate Excel, allowing the User to make changes to the Workbook. Then, when the User clicks the Button again, it updates all of those changes in Word. Is that right?

Yes.



Second, you say that the Workbook is accessed by multiple Users at once, therefore it has to be opened in the Read-Only state. Although this is not entirely true for having to open it as Read-Only (I'll get to that in a moment), why would the user need to be updating the Workbook if they can not save the changes? This doesn't make sense to me.

I know this sounds weird, but actually the user does not really modify the Excel file. It's more like a setting.


Regarding opening it as Read-Only... If the User needs to actually save the Workbook before returning to Word, you should look into setting the Workbook as "Shared with Tracking Changes." This will allow multiple Users to access it and save their data when they are done. There are warnings I'll give you if you choose to go that way.

I was not aware of this possibility. It's definitely worth a check !



Now, for having your Button available at before and after opening Excel... If you put the Button on a small Form, you can set the Form to be Modeless and "Always On Top." That will ensure the User can see this Form and Button even with Excel activated and select the Button once they are complete with the updates.

Mmmmh, it seems that my previous posts were not accurate enough. Actually, I do not have problems with the button of the Word file, because it is located on a new command bar. But sometimes the Excel file opens but the window is not activated, so it remains "hidden" behind the Word window, so the user may think that nothing happens.

I've sent you the files, I hope it helps !

Again, thank you very much for your help.

[shimuno]

Here are the files.

They comprises other macros, which seem to work fine. The one I am talking about is in the Word document and is called MAJ Liaisons.

[GTO]

Hello again,

Due to my lacking French and the Word file attempting to load a non-existant .xla, I greatly simplified what was going on during opening. Thus, didn't want you inadvertantly overide your existing, so here is the code to the Sub we discussed.

Please copy/paste it as is, as GetObject should be run first as aforementioneded.

I believe this should work without a hitch and hope it helps,

Mark
Sub MAJLiaisons()
'Proc?dure ? suivre si erreur
Dim xlApp As Object, _
xlWB As Object, _
bolWB_IsOpen As Boolean
On Error Resume Next

Set xlApp = GetObject(, "Excel.Application")
'// Ensure no existing err.number exists //
Err.Clear
'// Change error handling to handle inline. //
On Error Resume Next
'// Here is where I believe you ran into occassional 'issues'. Let's check //
'// to see if Excel already is running first. //
Set xlApp = GetObject(, "Excel.Application")
'// If Excel was not running, we just raised an error, so we'll use this to //
'// tell us we need to create Excel. //
If Err.Number > 0 Then
'// Clear the error //
Err.Clear
'// Create Excel, and make it visible. //
Set xlApp = CreateObject("Excel.Application")
xlApp.Application.Visible = True
xlApp.AskToUpdateLinks = False 'D?sactivation de la mise ? jour automatique

'// Since Excel wasn't running, we know we have to open the wb. //
xlApp.Application.Workbooks.Open _
FileName:="H:\OBLIGS\Breve\Modeles\Champs automatiques.xls", ReadOnly:=True
'// Reset error handling, explicitly release the object from memory, //
'// and exit the sub. //
On Error GoTo 0
Set xlApp = Nothing
Exit Sub
End If
'// Now if we made it here, we found that Excel already was running, so //
'// we'll reset error handling... //
On Error GoTo 0 '? Permet de ne pas tenir compte d'une erreur ?ventuelle (Sorry
' do not know French whatsoever. //
'// ...and make sure the app is visible. //
xlApp.Application.Visible = True
'// Now we'll go thru and see if the workbook is already opened. //
For Each xlWB In xlApp.Application.Workbooks
If xlWB.Name = "Champs automatiques.xls" Then
'// If "Champs Automatics.xls" is already opened, note this and //
'// exit the loop. //
bolWB_IsOpen = True
Exit For
End If
Next xlWB
'// If it wasn't open... //
If Not bolWB_IsOpen Then

xlApp.AskToUpdateLinks = False 'D?sactivation de la mise ? jour automatique
'// ...open it and set XL's window state //
xlApp.Application.Workbooks.Open _
FileName:="H:\OBLIGS\Breve\Modeles\Champs automatiques.xls", ReadOnly:=True
xlApp.Application.WindowState = -4137
Else
'// If it was already open, increase window state to maximized. //
xlApp.Application.WindowState = -4137
End If

ActiveDocument.Fields.Update

'// The other thing you could do (you mentioned the workbook sometimes //
'// being open, but the user being unaware as it was 'hidden') is to //
'// reduce Word to an icon. //
Application.WindowState = wdWindowStateMinimize

'// Explicitly release object. //
Set xlApp = Nothing
End Sub

[GTO]

Here Shimuno,

I didn't think to send it as a module, and this might be easier...

Mark

[shimuno]

Well, everything seems to work perfectly fine. GTO and Demosthine, thank you very much for your help. Really, thank you.

[GTO]

Happy to help and glad it's working now.

Since it is solved, please mark the thread 'Solved'. There's a button marked 'thread tool' that has this option.

Mark

[shimuno]

I only see "Show printable version", "email this page" and "subscribe to this thread"...

[GTO]

I only see "Show printable version", "email this page" and "subscribe to this thread"...


Hi Shimuno,

When you open the thread, look above your first post. Not here where you are reading this, but there's a green bar above your original question that has the command.

Have a great day!

Mark