Transferring Content from Word Doc to Excel Doc - checking if already done

[illogic]

Hello,

i have a word document which, upon saving, transfers the content of the word doc to a excel list.
For each document written and saved, the excel list starts a new row.

Now my problem is that the document transfers that content all the time. Regardless if that document has already been saved and the content has already been transfered. It creates duplicate entries, which i seek to avoid.

Is it possible to creat some sort of index number for the document and the excel list?
So that upon saving the document for the first time, it generates that index number for the word document and the row in the excel list it transfers the data to.

When the document is opened and saved for a second time, it uses that already created index number and notifies the user that this documents content has already been transfered to the excel list. It asks the user if the content needs to be transfered again and if the user confirms to translate the data again, the document is looking in the excel list for that specific index and overwrites the data in that row.

I hope i explained it well enough so that it is clear what i am trying to achieve.


greetings

Manuel

[gmayor]

As this appears to be done using a macro to write to the workbook, a simple solution might be to write and save document variable to the document (or custom document property) when it is transferred to Excel and to look for that variable (or property) when saving so that if present the copy to Excel is omitted.

[illogic]

Ok this is what i got so far.

The Code for the "required fields" in "This Document". This Code is used to check all required fields for completeness.


Sub FilePrintDefault()
If Checkfields = True Then

ActiveDocument.PrintOut
End If
End Sub

Sub FilePrint()
FilePrintDefault
End Sub

Sub FileSave()
If Checkfields = True Then
If ActiveDocument.Path = "" Then
FileSaveAs
End If
Word2Excel.DataTransfer
ActiveDocument.Save
End If
End Sub

Sub FileSaveAs()
If Checkfields = True Then
Dialogs(wdDialogFileSaveAs).Show
End If
End Sub

What then follows is a lot of code for all the required fields. (example)



Private Function Checkfields() As Boolean
Application.ScreenUpdating = False

...

If A1.Value = False And A2.Value = False Then
MsgBox "Bitte Häkchen für Erstbescheinigung oder Folgebescheinigung setzen!"
Checkfields = False
Exit Function
ElseIf A1.Value = True And A2.Value = True Then
Exit Function
End If

...

Checkfields = True
Application.ScreenUpdating = True
End Function


Then i have a module for the data transfer called word2excel, which contains the following.


Sub DataTransfer()
Dim xlApp As Object
Dim xlWBook As Object
Dim fld As FormField
Dim nRow As Long
Dim nCol As Integer
Dim ws As Object
Dim ldfNr As Integer
Const xlUp = -4162

Application.ScreenUpdating = False

Set xlApp = CreateObject("excel.Application")
Set xlWBook = xlApp.Workbooks.Open(ThisDocument.Path & "\artexGeräteliste.xlsx")
xlWBook.Application.Visible = True
xlWBook.Application.Sheets("Geräteübersicht").Select
Set ws = xlWBook.Sheets("Geräteübersicht")
nRow = ws.Cells(ws.Rows.Count, 4).End(xlUp).Row + 1
ldfNr = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

If ldfNr = 0 Then
ws.Cells(ldfNr + 1, 1) = 1
Else
ws.Cells(ldfNr + 1, 1) = ldfNr - 2
End If

Cells(nRow, 1).RowHeight = 18


...followed by lots of code to determin which data to transfer and where to put it. (Example)



...
nInstall = ActiveDocument.FormFields("Gebäude").Result & " - " & ActiveDocument.FormFields("Ebene").Result
xlWBook.Application.Cells(nRow, 2).Value = ActiveDocument.FormFields("Gebäude").Result & " - " & ActiveDocument.FormFields("Ebene").Result
...


...and what to do when all data is transfered.



nrow1 = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row - nRow
If nrow1 > 0 Then
Cells(nRow + 1, 1).Resize(nrow1).EntireRow.Delete
End If
Application.ScreenUpdating = True
xlWBook.Close SaveChanges:=True
Application.Quit SaveChanges:=True

End Sub


Now i could use some help creating the variables to creat some sort of index as described above.
Any ideas?


greetings

Manuel

[illogic]

No one having any tips how to go about this? Would it even be possible to integrate such a function into this code or am i completely on the wrong track?

[gmayor]

To go back to your original question my suggestion could be implemented by changing the macro below as follows.
The macro will not transfer the document to Excel is the variable is present.


Sub FileSave()
Dim oVar As Variable
Dim bVar As Boolean
If Checkfields = True Then
If ActiveDocument.Path = "" Then
FileSaveAs
End If
For Each oVar In ActiveDocument.Variables
If oVar.Name = "varTransferred" Then
bVar = True
Exit For
End If
Next oVar
If Not bVar Then
Word2Excel.dataTransfer
ActiveDocument.Variables("varTransferred").Value = True
End If
ActiveDocument.Save
End If
End Sub

[illogic]

Ok thanks for the quick reply.
I tried your solution and the document transferred the data as usual, then closed the document after the transfer.
But when i reopen the document and save again, it just transfers the data to excel again, as if nothing changed at all.

Any idea why that is?
Maybe i am just stupid atm. Long day... =)

[gmayor]

The FileSave macro does not close the document. If your supplementary code closes the document, the instruction to save the document with the variables is not activated. The variable is not saved and so there is nothing to stop it being transferred again. Thus the variables should be added and the document saved before the transfer takes place e.g.


Sub FileSave()
Dim oVar As Variable
Dim bVar As Boolean
If Checkfields = True Then
If ActiveDocument.Path = "" Then
FileSaveAs
End If
For Each oVar In ActiveDocument.Variables
If oVar.Name = "varTransferred" Then
bVar = True
Exit For
End If
Next oVar
If Not bVar Then
ActiveDocument.Variables("varTransferred").Value = True
ActiveDocument.Save
Word2Excel.dataTransfer
End If
If Not ActiveDocument.Saved Then ActiveDocument.Save
End If
End Sub

[illogic]

Thanks for the reply, you are a great help to me.
Now it is working. After the document has been saved once, it no longer transfers the data to the excel workbook.
It doesn't save at all, until i change some content in the document which isn't bad but...

If content changes the data should be written to the excel workbook again but the document should recognize that it has been saved before and ask if the content should be transferred again. If the user confirms to save again, it should not simply transfer the data to a new row, but find the initial row the data has been transfered to and overwrite that data. And it should do so, every time a change to the document is made.

This would be awesome!

In order to achieve that, i guess i would have to edit the actual word2Excel module.
So when the data is being transferred, it writes something to a specific cell in the row that the macro generates when transferring the data.
Something unqiue to that row, so that the Word2Excel macro knows which content to overwrite.

This is the code for the word2excel module:


Sub DataTransfer()
Dim xlApp As Object
Dim xlWBook As Object
Dim fld As FormField
Dim nRow As Long
Dim nCol As Integer
Dim ws As Object
Dim ldfNr As Integer
Const xlUp = -4162

Application.ScreenUpdating = False

Set xlApp = CreateObject("excel.Application")
Set xlWBook = xlApp.Workbooks.Open(ThisDocument.Path & "\artexGeräteliste.xlsx")
xlWBook.Application.Visible = True
xlWBook.Application.Sheets("Geräteübersicht").Select
Set ws = xlWBook.Sheets("Geräteübersicht")
nRow = ws.Cells(ws.Rows.Count, 4).End(xlUp).Row + 1
ldfNr = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

If ldfNr = 0 Then
ws.Cells(ldfNr + 1, 1) = 1
Else
ws.Cells(ldfNr + 1, 1) = ldfNr - 2
End If

Cells(nRow, 1).RowHeight = 18
...
nInstall = ActiveDocument.FormFields("Gebäude").Result & " - " & ActiveDocument.FormFields("Ebene").Result
xlWBook.Application.Cells(nRow, 2).Value = ActiveDocument.FormFields("Gebäude").Result & " - " & ActiveDocument.FormFields("Ebene").Result
...
nrow1 = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row - nRow
If nrow1 > 0 Then
Cells(nRow + 1, 1).Resize(nrow1).EntireRow.Delete
End If
Application.ScreenUpdating = True
xlWBook.Close SaveChanges:=True
Application.Quit SaveChanges:=True

End Sub


Any suggestions how to achieve this?

Again, i am very grateful for your help and all the time you spend to help me out, thank you very much!


greetings

Manuel

[gmayor]

Are you able to confirm the row number for existing data is not going to change? If the row number is fixed, you can record the row number in the variable and transfer the data for all documents. If the variable exists, write the values to the row number from the variable. If the variable is not poresent, write to the next available row. If the row number might change, you will have to perform a look up to see if the record exists. In order to do that we would need information on what to look for that is unique to the record, and where in the row that information is stored.

[illogic]

Normaly the rows should not change after the data has been transferred to the workbook.
But it could be the case, so i think your suggestion with the look up would be the more adequate solution.
I created a new column in the workbook. It is now column A. So the unique entries could be stored in that column.
The first three rows of the workbook contain static data which doesn't change.
Row Nr. 4 is the first row where the data is beeing transferred into.

17312

I don't know what possibilities exist to generate unique entries without duplicates.
Maybe a combination of numbers and letters?
Could the code from the word2excel module that i posted above be used if adjusted?

[gmayor]

The problem is that if you don't know what makes a record unique, neither will the macro. If you are going to use a lookup then you are going to have to look for matching data from the values you are adding, in the records that have already been added, so that the macro can make a decision whether to add it or not. As only you know what the worksheet contains and what the values are, only you can decide what to check to determine whether the record should be added.

[illogic]

Ok i understand what you mean.
I first thought when i came up with this idea that it would be possible to let the macro generate something like a unique number inside the document when it is saved for the first time, which is then transferred to the list with all the other data from the document.
It kind of does that already with the "varTransferred variable" if i interpret your code correctly? Couldn't this be modiefied to check the document itself if it has already generated such a unique number for itself?

If there is no number - generate a number for the active document and transfer it with the rest of the data to the excel list.
if there is a number - tell the user that the document has already been saved - ask if the data needs to be replaced or if the action should be canceled
if the user confirms to overwrite then look for the number generated and replace the data in that specific row.
If canceled, abort the action.

Just for clarification:
These documents are reports for safety valves. These valves need to have maintenance which needs to be logged.
So each of these documents/reports is written once and the data for an overview transferred to an excel list.
After a report has been written, the content might change if there is something done to the valve after the maintenance, like a repair.
That repair needs to be logged in the already written report and some of the data might change. so it would be necessary to edit the data inside the excel list by hand.
It would be easier to just edit the data inside the report and let the macro change the excel list automatically.
For each report a blank template is used all the time. So every document wouldn't have that unique number at the beginning.

[gmayor]

OK, I think we are getting somewhere. Can we add another column to the worksheet to store the ID that will be stored in the variable? If so which column would that be? If you name that column varID we can store the same unique ID as in the document then look for the row that contains the ID number. No variable and a new number is generated and added both to the variable and to the new record.

I have assumed that the column with the ID numbers is A, change as appropriate. Obviously I can't test this. I don't have your documents or your worksheet, but it should be close:


Option Explicit

Sub FileSave()
Dim oDoc As Document
Dim oVar As Variable
Dim lngID As Long
Set oDoc = ActiveDocument
If Checkfields = True Then
If oDoc.Path = "" Then
FileSaveAs
End If
For Each oVar In oDoc.Variables
If oVar.Name = "varID" Then
lngID = oVar.Value
Exit For
End If
Next oVar
If Not bVar Then
oDoc.Variables("varID").Value = "0"
oDoc.Save
End If
DataTransfer CStr(lngID)
If Not oDoc.Saved Then oDoc.Save
End If
End Sub

Sub DataTransfer(sID As String)
Dim xlApp As Object
Dim xlWBook As Object
Dim fld As FormField
Dim nRow As Long
Dim nCol As Integer
Dim ws As Object
Dim ldfNr As Integer
Dim NextID As Long
Const xlUp = -4162

Application.ScreenUpdating = False

Set xlApp = CreateObject("excel.Application")
Set xlWBook = xlApp.Workbooks.Open(ThisDocument.Path & "\artexGeräteliste.xlsx")
xlWBook.Application.Visible = True
xlWBook.Application.Sheets("Geräteübersicht").Select
Set ws = xlWBook.Sheets("Geräteübersicht")

If sID = "0" Then
nRow = ws.Cells(ws.Rows.Count, 4).End(xlUp).Row + 1
'Column A is the column with the ID numbers - change as apppropriate
NextID = xlApp.WorksheetFunction.Max(Range("A:A")) + 1
ActiveDocument.Variables("varID").Value = NextID
ActiveDocument.Save
Else
On Error Resume Next
'Column A is the column with the ID numbers - change as apppropriate
nRow = xlApp.WorksheetFunction.Match(CLng(sID), Range("A:A"), 0)
On Error GoTo 0
End If
ldfNr = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

If ldfNr = 0 Then
ws.Cells(ldfNr + 1, 1) = 1
Else
ws.Cells(ldfNr + 1, 1) = ldfNr - 2
End If

Cells(nRow, 1).RowHeight = 18
...
nInstall = ActiveDocument.FormFields("Gebäude").Result & " - " & ActiveDocument.FormFields("Ebene").Result
xlWBook.Application.Cells(nRow, 2).Value = ActiveDocument.FormFields("Gebäude").Result & " - " & ActiveDocument.FormFields("Ebene").Result
...
nrow1 = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row - nRow
If nrow1 > 0 Then
Cells(nRow + 1, 1).Resize(nrow1).EntireRow.Delete
End If
Application.ScreenUpdating = True
xlWBook.Close SaveChanges:=True
Application.Quit SaveChanges:=True

End Sub

[illogic]

Ok i implemented the code named the column A in the worksheet to varID and tested it.

There are a few problems,
when i just run the code, i get bVar variable not defined.
I fixed that with Dim bVar as Variable.

When i run the code again, i get the following error:
"object variable or with block variable not set"

So for an overview, the code now looks like this:


Option Explicit

Sub FileSave()
Dim oDoc As Document
Dim oVar As Variable
Dim bVar As Variable
Dim lngID As Long
Set oDoc = ActiveDocument
If Checkfields = True Then
If oDoc.Path = "" Then
FileSaveAs
End If
For Each oVar In oDoc.Variables
If oVar.Name = "varID" Then
lngID = oVar.Value
Exit For
End If
Next oVar
If Not bVar Then
oDoc.Variables("varID").Value = "0"
oDoc.Save
End If
DataTransfer CStr(lngID)
If Not oDoc.Saved Then oDoc.Save
End If
End Sub



And the word2Excel module



Sub DataTransfer(sID As String)
Dim xlApp As Object
Dim xlWBook As Object
Dim fld As FormField
Dim nRow As Long
Dim nCol As Integer
Dim ws As Object
Dim ldfNr As Integer
Dim NextID As Long
Const xlUp = -4162

Application.ScreenUpdating = False

Set xlApp = CreateObject("excel.Application")
Set xlWBook = xlApp.Workbooks.Open(ThisDocument.Path & "\artexGeräteliste.xlsx")
xlWBook.Application.Visible = True
xlWBook.Application.Sheets("Geräteübersicht").Select
Set ws = xlWBook.Sheets("Geräteübersicht")

If sID = "0" Then
nRow = ws.Cells(ws.Rows.Count, 4).End(xlUp).Row + 1
'Column A is the column with the ID numbers - change as apppropriate
NextID = xlApp.WorksheetFunction.Max(Range("A:A")) + 1
ActiveDocument.Variables("varID").Value = NextID
ActiveDocument.Save
Else
On Error Resume Next
'Column A is the column with the ID numbers - change as apppropriate
nRow = xlApp.WorksheetFunction.Match(CLng(sID), Range("A:A"), 0)
On Error GoTo 0
End If
ldfNr = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row

If ldfNr = 0 Then
ws.Cells(ldfNr + 1, 2) = 1
Else
ws.Cells(ldfNr + 1, 2) = ldfNr - 2
End If




Don't know exactly what is causing the problem.

*Edit*

I had to change some code, because i previously had a continuing number in column A which is now column B, so i changed some numbers.


ldfNr = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row

If ldfNr = 0 Then
ws.Cells(ldfNr + 1, 2) = 1
Else
ws.Cells(ldfNr + 1, 2) = ldfNr - 2
End If

But the "object variable or with block variable not set" problem still exists.

[gmayor]

I managed to accidentally edit out bVar :( It goes where shown

I didn't intend that you change Column A, but add another column for the ID and change the column in the code where indicated to that new column. This new column is only a check to see if the data has been previously saved so that it can be used in a look up (which I also added)

Don't remove Option Explicit. Put it back as it forces you to declare your variables. Compile the project and you should see where the error lies.

The added code is just a lookup to determine whether to add the document at the end or to use an existing record. Clearly it will only work if you start a new sheet as the existiung entries and their documents don't have the IDs.

There is a limit to what I can do without access to the workbook and the document sample.


Option Explicit

Sub FileSave()
Dim oDoc As Document
Dim oVar As Variable
Dim bVar As Variable
Dim lngID As Long
Set oDoc = ActiveDocument
If Checkfields = True Then
If oDoc.Path = "" Then
FileSaveAs
End If
For Each oVar In oDoc.Variables
If oVar.Name = "varID" Then
lngID = oVar.Value
bVar = True 'Add this line
Exit For
End If
Next oVar
If Not bVar Then
oDoc.Variables("varID").Value = "0"
oDoc.Save
End If
DataTransfer CStr(lngID)
If Not oDoc.Saved Then oDoc.Save
End If
End Sub

[illogic]

Hmm this is very strange, i still get that "object variable or with block variable not set" error message, even after completing the code as you mentioned.
Compiling the macro didn't do anything for me.
I will PM the document and worksheet to you.
I entered all nessecary fields in the document with "test". The document and excel list need to be in the same folder.
There is a field in the document called "Sicherungsart" if you click on that, you should get an error because there a image files that you would need which i won't send with the document.
But normaly that should not be a problem to test the code.
if something does not work, just tell me. I will try to react accordingly.

And sorry that i am bugging you with this stuff so much. I thought it would be a bit less problematic. :bug:

*Edit*
Ups just found out i can't send any PMs to you. Do you have a email adress to which i could send the documents?

[gmayor]

You can send me the document and workbook to supportATgmayor.com. Put your forum username in the subject or the message will be discarded.

[illogic]

I implemented the new code, declared the variables as you mentioned, compiled the macro and tested it.
The macro now runs trough the code without any errors but the column A "varID" is still empty after the data transfer.
I checked the module which transfers the data if there is anything that overwrites column a after the ID has been stored but couldn't find anything.

When you did run the macro, did it store the ID in column A? I wonder if its just me, or if there is still something going wrong with the macro.

[gmayor]

I must be off my game this week :( I had overlooked adding the number to column A


If sID = "0" Then
nRow = ws.Cells(ws.Rows.Count, 4).End(xlUp).Row + 1
'Column A is the column with the ID numbers - change as apppropriate
NextID = xlApp.WorksheetFunction.Max(ws.Range("A:A")) + 1
'add the new ID to column A
ws.Cells(nRow, 1) = NextID
ActiveDocument.Variables("varID").Value = NextID
ActiveDocument.Save
Else

[illogic]

Oh thats no problem at all.
So far it is working now, the ID gets stored in column A.
So a big thanks for that.

I still have a few questions.

The variable for the ID that is beeing written in the document, where is that actually stored?
Is it possible to reset this ID somehow if needed?

Would it be a huge effort to implement a query which gives a response if the document has been saved and asks if the data should be transferred again?
Right now it just overwrites the data on and on without notice.

If it is to much work its ok, i can guess that you are very bussy.

[gmayor]

The ID is stored in a docvariable called varID in the document itself. You could write a new value to the variable, or you could delete the variable. For ease of access you could use http://www.gmayor.com/BookmarkandVariableEditor.htm which will do either.

You can also add a a prompt if the variable exists in the document e.g.


Sub FileSave()
Dim oDoc As Document
Dim oVar As Variable
Dim bVar As Boolean
Dim lngID As Long
Dim lngAsk As Long
Set oDoc = ActiveDocument
If Checkfields = True Then
If oDoc.Path = "" Then
FileSaveAs
End If
For Each oVar In oDoc.Variables
If oVar.Name = "varID" Then
lngAsk = MsgBox("This record appears to have been recorded." & vbCr & _
"Do you wish to edit the original entry?", vbYesNoCancel)
Select Case lngAsk
Case vbCancel
MsgBox "Save cancelled"
GoTo lbl_Exit
Case vbNo
bVar = False
Exit For
Case vbYes
lngID = oVar.Value
bVar = True
Exit For
End Select
End If
Next oVar
If Not bVar Then
oDoc.Variables("varID").Value = "0"
oDoc.Save
End If
dataTransfer CStr(lngID)
If Not oDoc.Saved Then oDoc.Save
End If
lbl_Exit:
Exit Sub
End Sub

[illogic]

Hello Graham,
regarding your Variables Editor:

sadly the variables editor doesn't work for me.
It works through the document and then responds mit an error code. "Run-time error '5941' the requested member of the collection does not exist."
I guess it is due to some of my code. Debugging is greyed out so i am not able to see what exatcly is causing this.
Is there a method of deleting a variable by hand? I read somewhere that i should edit the xml of a word doc by changing the doc to zip and open the settings.xml in the word folder. But i don't have any option to open xml files in any editor. i can just view them in internet explorer.

Regarding the new macro:

Thanks for modifying the macro once more,
it gets closer and closer to where it should be, but still isn't quite there yet.

I declared vbYes, vbNo and vbCancel as Boolean. I hope that i didn't do anything wrong here.
Before that, it just gave me an error when compiling.
After running the macro it detects that the document has been saved before, responds with a message box and gives the options to choose from.
But no matter which option is chosen, the result is all the same.
• 'Yes' transfers the data, when no data is present in the worksheet it writes to the first row and stores the id as number 1 in column A.
• When i save the same document again and click 'Yes', it doesn't overwrite the existing entry but writes to a new row storing the id as number 2 in column A.
• 'No' is actually not needed.
• When i click 'Cancel' it does the same as with 'Yes' when it should just cancel the action and not do anything at all.

And the Message boxes don't show up when one of the cases is selected.
Here is what i did


Sub FileSave()
Dim oDoc As Document
Dim oVar As Variable
Dim bVar As Boolean
Dim lngID As Long
Dim lngAsk As Long
Dim vbCancel As Boolean, vbNo As Boolean, vbYes As Boolean
Set oDoc = ActiveDocument
If Checkfields = True Then
If oDoc.Path = "" Then
FileSaveAs
End If
For Each oVar In oDoc.Variables
If oVar.Name = "varID" Then
lngAsk = MsgBox("Das Prüfprotokoll wurde bereits in die Geräteliste exportiert." & vbCr & _
"Wurden Daten im Protokoll abgeändert, kann der Eintrag in der Geräteliste mit den aktuellen Daten überschrieben werden!" & vbCr & _
"Wähle 'Abbrechen' um den Speichervorgang abzubrechen!" & vbCr & _
"Wähle 'Nein' um das Dokument zu speichern ohne Daten zu exportieren!" & vbCr & _
"Wähle 'Ja' um den Eintrag in der Geräteliste mit den aktuellen Daten zu überschreiben!", vbYesNoCancel)
Select Case lngAsk
Case vbCancel
MsgBox "Speichervorgang abgebrochen!"
GoTo lbl_Exit
Case vbNo
MsgBox "Dokument gespeichert"
bVar = False
Exit For
Case vbYes
MsgBox "Daten werden überschrieben"
lngID = oVar.Value
bVar = True
Exit For
End Select
End If
Next oVar
If Not bVar Then
oDoc.Variables("varID").Value = "0"
oDoc.Save
End If
DataTransfer CStr(lngID)
If Not oDoc.Saved Then oDoc.Save
End If
lbl_Exit:
Exit Sub
End Sub


Actually when i think about it, i wouldn't need 3 options to choose from at save.
'Yes' when the data should be transferred
And 'Cancel' when save should be aborted.
If 'Yes' is chosen, it should check if data in the doccument has been modified.
If data has been modified, the it should transfer the data.
If there is no modified data i should not transfer data and respond with a message, lets say something like "There is no new data to transfer."

greetings

Manuel

[illogic]

Hi,

until now i wasn't able to find out why it actually doesn't abort the action when canceled and instead just transfers the data again and why the message boxes won't show up.
Do you have an idea why this may be the case?

[illogic]

Hello gmayor,

i solved my problem with the case select statement,
it seems to work now, but to further test if everything is working correct i would need to empty the variable.
I still can't get your Editor to run with my document. It always gives me that 5941 run time error.
Is this due to my macros in the document or could anything else cause this error?

[gmayor]

I don't know why the add-in is not working for you. I have been unable to reproduce the error. However you could easily clear the variable with a macro.


Option Explicit

Sub ClearVariable()
Dim oVar As Variable
Dim bVar As Boolean
For Each oVar In ActiveDocument.Variables
If oVar.Name = "varID" Then
bVar = True
oVar.Delete
MsgBox "Variable Deleted"
Exit For
End If
Next oVar
If Not bVar Then MsgBox "Variable not found"
lbl_Exit:
Set oVar = Nothing
Exit Sub
End Sub

[illogic]

Ah wonderful,
thought it would be a bit more complex.
Thanks for helping me out!

greetings

Manuel

[illogic]

Hello,

i tried to solve the Problem that i had with my Case Select Statement.
I changed some stuff because i found that the first solution wasn't exactly fit for what i need.
But i have a Problem with the code. The Document, at save, doesn't recognize that the variable isn't there.
It just transfers the data and and creates the variable without the case select to take effect.

Maybe i am just completely on the wrong track here... but i am little lost.
No idea what is going wrong here.

Maybe someone could take a look at it.



Sub FileSave()
Dim oDoc As Document
Dim oVar As Variable
Dim bVar As Boolean
Dim lngID As Long
Dim lngAsk As Long
Dim weiter2 As Boolean
Dim vbCancel As Boolean, vbNo As Boolean, vbYes As Boolean, weiter As Boolean
Set oDoc = ActiveDocument
If Checkfields = True Then
If oDoc.Path = "" Then
FileSaveAs
End If
For Each oVar In oDoc.Variables
If oVar.Name = "varID" Then
lngAsk = MsgBox("Die Daten dieses Protokolls wurden bereits in die Geräteliste übertragen!" & vbCr & _
"Wurden Daten im Protokoll geändert, kann der Eintrag in der Geräteliste aktualisiert werden." & vbCr & _
vbCr & _
"• Wähle 'Ja' um den vorhandenen Eintrag zu aktualisieren!" & vbCr & _
"• Wähle 'Nein' um das Dokument ohne Datenübertragung zu speichern!" & vbCr & _
"• Wähle 'Abbrechen' um den Vorgang zu beenden!", vbYesNoCancel)
weiter = True
weiter2 = False
Else
If oVar.Name = "" Then
lngAsk = MsgBox("Die Daten dieses Protokolls wurden noch nicht in die Geräteliste übertragen!" & vbCr & _
"Es besteht die Möglichkeit dies zu tun oder das Dokument ohne Datenübertragung zu speichern." & vbCr & _
vbCr & _
"• Wähle 'Ja' um die Daten in die Geräteliste zu übertragen und das Protokoll zu speichern!" & vbCr & _
"• Wähle 'Nein' um das Dokument ohne Datenübertragung zu speichern!" & vbCr & _
"• Wähle 'Abbrechen' um den Vorgang zu beenden!", vbYesNoCancel)
weiter = True
weiter2 = True
Else
weiter = False
weiter2 = False
End If
End If
If weiter = True And weiter2 = False Then
Select Case lngAsk
Case 6 'vbYes
MsgBox ("Die Daten werden jetzt überschrieben und das Dokument gespeichert!")
lngID = oVar.Value
bVar = True
Exit For
Case 7 'vbNo
MsgBox ("Das Protokoll wird ohne Datenübertragung gespeichert!")
bVar = False
Exit For
Case 2 'vbCancel
MsgBox ("Speichervorgang wird abgebrochen!")
GoTo lbl_Exit
Case Else
MsgBox "Es ist etwas schief gelaufen!"
GoTo lbl_Exit
End Select
ElseIf weiter = True And weiter2 = True Then
Select Case lngAsk
Case 6 'vbYes
MsgBox ("Die Daten werden jetzt in die Geräteliste übertragen und das Protokoll gespeichert!")
lngID = oVar.Value
bVar = True
Exit For
Case 7 'vbNo
MsgBox ("Das Protokoll wird ohne Datenübertragung gespeichert!")
bVar = False
Exit For
Case 2 'vbCancel
MsgBox ("Speichervorgang wird abgebrochen!")
GoTo lbl_Exit
Case Else
MsgBox "Es ist etwas schief gelaufen!"
GoTo lbl_Exit
End Select
End If
Next oVar
If Not bVar Then
oDoc.Variables("varID").Value = "0"
oDoc.Save
Else
DataTransfer CStr(lngID)
End If
If Not oDoc.Saved Then oDoc.Save
End If
lbl_Exit:
Exit Sub
End Sub




Greets

Manuel

[illogic]

Doesn't anyone have an idea what i am doing wrong here?

[gmayor]

Where to start? The macro begins with

If Checkfields = True Then
There is no indication what this refers to and the only reference I spotted in earlier messages appears to relate to Excel? As it stands, if the macro doesn't crash because of the undeclared variable it will never be true so the rest won't run.

The syntax for checking if a named variable exists, as I showed in my earlier example is

bVar=False 'The boolean value must be false to begin with. It will be, unless it has previously been set to True.
For Each oVar In oDoc.Variables
If oVar.Name = "varID" Then
bVar = True 'The variable exists"
'so do something here related to the variable
Exit For ' the variable is found, so stop looking
End If
Next oVar
If Not bVar Then
'The variable wasn't present so do something else here
End IfYour doesn't seem to have much relationship to this.

[illogic]

Well i have some required fields in my document which need to be filled in or the macro will give an error message.

The original macro was:


Sub FileSave()
If Checkfields = True Then
If ActiveDocument.Path = "" Then
FileSaveAs
End If
Word2Excel.DataTransfer
ActiveDocument.Save
End If
End Sub

followed by the actual checkfields function: (if something isn't filled in the way i want it to the macro responds with error messages. If all fields are filled in correctly, it sets the variable to true and the data transfer starts)


Private Function Checkfields() As Boolean
Application.ScreenUpdating = False

If A1.Value = False And A2.Value = False Then
MsgBox "Bitte Häkchen für Erstbescheinigung oder Folgebescheinigung setzen!"
Checkfields = False
Exit Function
ElseIf A1.Value = True And A2.Value = True Then
Exit Function
End If

If O1A.Value = False And O1B.Value = False Then
MsgBox "Bitte Häkchen bei vor Ort Überprüfung oder Werkseitige Überprüfung setzen!"
Checkfields = False
Exit Function
ElseIf O1A.Value = True And O1B.Value = True Then
Exit Function
End If

... (code shortened)

Checkfields = True
Application.ScreenUpdating = True
End Function


Then after alle fields have been checked and no errors appear, the macro executes "Word2Excel.DataTransfer"
Which then transfers the Data from the Word Document to an Excel sheet.

The Problem is, the Code you provided to me was good so far.
It created the variable at save and at the second, third etc. save it started the select case which then gave the options to either transfer the data, or just save the document without saving etc.

I wanted to add a second select case which showed up every time the document is saved for the first time giving options to either transfer the data or just save the document and so on.

So that there would be two cases.

a. Document saved for the first time (variable not there)

Option 1: Create the variable, transfer data and save document
Option 2: Save the document without data transfer and without creating the variable.
Option 3: Cancel

b. Document has been saved before (variable exists)

Option 1: transfer data to the same entry in the excel sheet and overwrite the existing data and save word document.
Option 2: Save the document without updating the data.
Option 3: Cancel

So your original Code was:


Sub FileSave()
Dim oDoc As Document
Dim oVar As Variable
Dim bVar As Boolean
Dim lngID As Long
Dim lngAsk As Long
Dim vbCancel As Boolean, vbNo As Boolean, vbYes As Boolean
Set oDoc = ActiveDocument
If Checkfields = True Then
If oDoc.Path = "" Then
FileSaveAs
End If
For Each oVar In oDoc.Variables
If oVar.Name = "varID" Then
lngAsk = MsgBox("Das Prüfprotokoll wurde bereits in die Geräteliste exportiert!" & vbCr & _
vbCr & _
"Wurden Daten im Protokoll geändert, kann der Eintrag in der Geräteliste aktualisiert werden!" & vbCr & _
vbCr & _
"• Wähle 'Ja' um den Eintrag zu aktualisieren!" & vbCr & _
"• Wähle 'Nein' um das Dokument ohne aktualisierung zu speichern!" & vbCr & _
"• Wähle 'Abbrechen' um den Vorgang zu beenden!", vbYesNoCancel)
Select Case lngAsk
Case vbYes
MsgBox ("Daten werden überschrieben")
lngID = oVar.Value
bVar = True
Exit For
Case vbNo
MsgBox ("Dokument wird gespeichert")
bVar = False
Exit For
Case vbCancel
MsgBox ("Speichervorgang abgebrochen")
GoTo lbl_Exit
End Select
End If
Next oVar
If Not bVar Then
oDoc.Variables("varID").Value = "0"
oDoc.Save
End If
DataTransfer CStr(lngID)
If Not oDoc.Saved Then oDoc.Save
End If
lbl_Exit:
Exit Sub
End Sub

And i tried to modify it on my own without any luck. My knowledge of VBA is just to limited ...
But i hope you understand what

If Checkfields = True Then
is doing. It just checks if all formfields are filled and then starts the data transfer if successful.

[gmayor]

Unfortunately the code you have quotes as the Checkfields function doesn't actually check form fields.
It is not clear what A1, A2, 01A and 01B refer to.
If they are form fields the syntax is wrong and should be something like

Dim off As FormField
Dim bFF As Boolean
bFF = False
Set off = ActiveDocument.FormFields("A1")
bFF = off.CheckBox.Value
If bFF = True Then
MsgBox "True"
Else
MsgBox "False"
End If