Looking for suggestions for a shared Office Suite Process

[BJZeak]

3 W7Pro PCs Peer to Peer with one Shared Folder on the PC with Office 2010
Office 2010
Office 2003
Office 2003

1 MS ACCESS 2003 mdb Application Backend Shared
maintains data used in Spreadsheet
1 MS EXCEL 2003 xls Workbook Shared
5 of 6 Sheets Protected ... all 5 automatically get their data from unprotected sheet

Attempted to directly update cells in the Shared unprotected Worksheet using a MS OFFICE EXCEL OBJECT but the object save is returning a 1004 Error ... not sure if this is a mixed version issue, a sharing issue or other ... doesn't appear to matter if the Shared Excel Workbook is already open or closed ... I have used the same object logic in this Access App to successfully create NEW workbooks from templates so not sure why there is an error for what seems to be a text book save request ... I made EXCEL visible to see if that would shed any light but Excel just hangs and never even gets to the error message

The ultimate goal is not to have to reenter data

- the ACCESS Application gets its info from Outlook attachments, (Website form data) and populates a temporary record which needs human review (a person on any one of the 3 PC's) ... once reviewed a token is assigned ... this token and the Web Data must link to data existing on the unprotected worksheet ... ultimately I am looking for some way to either update this worksheet once a token is assigned OR provide a way for the Spreadsheet to automatically see the Token located in the Access Database

I get that even in a shared mode there is a complexity with random writes to the spreadsheet ... so ... wondering if maybe a Cell might be able to link via a dblookup or query to data in access? Could possibly add VBA Code to Excel that would update the cell value (but then see there was some note to the effect that MACROS/VBA is not available in SHARED Workbooks?)

Current update code that causes the 1004 error

Function fnWS(SSID As String, WSID As String)
Dim sFN As String
Dim objApp As Object
Dim wb As Object
Dim inX As Long
Dim sX As String
Dim sZ As String

  Set objApp = CreateObject("Excel.Application")
  objApp.Visible = True ; both True and False have issues
  
  sFN = "s:\shared\sharedwb.xls"
  
  Set wb = objApp.Workbooks.Open(sFN, True, False) 

lp1:
  inX = inX + 1
  sX = "B" & inX
  sZ = wb.Sheets(2).Range(sX).Value
  If sZ & "" = SSID Then
    sX = "A" & inX
    wb.Sheets(2).Range(sX).Value = WSID
    GoTo lpe
  ElseIf sZ & "" = "" Then
    GoTo lpe
  End If
  GoTo lp1
lpe:
 [b]wb.Application.activeworkbook.Save[/b]
  wb.Application.activeworkbook.Close
  
  Set wb = Nothing
  Set objApp = Nothing

End Function

 

[SkipVought]

Hi,

1) you already have instantiated the Excel Application Object
2) you have already instantiated the wb Object which inherited the Excel Application Object

Therefore, you need not reference ANY application object when using the wb Object.

But there's more.

3) wb is the ONLY workbook object and is therefore the ActiveWorkbook
4) Depending on what application it is where your code is running (and I assume that it is NOT Excel, because you have created an Excel Application Object) the Application Object in the statement in question is NOT the Excel Application Object, but it is the application object of that application in which your code is running.

So...

wb.Save
wb.Close

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[BJZeak]

Thanks for pointing that out ... looking closer at the other instance of this logic it was being trapped by a non reporting error routine

ok so while this logic now updates the unprotected sheet, if someone has the spreadsheet open how do they reload the worksheet without shutting it down and reloading the workbook?

shared documentation suggests a dialog should pop up if a change occurs but not seeing that happen

ideas?

 

[combo]

It is possible that the error is because of setting the workbook as shared. Maybe not directly this issue, but here is described a problem referring to shared workbook:

https://support.microsoft.com/en-au...e-a-vba-macro-to-save-a-shared-excel-2007-wor

I haven't tested the proposed solution, but I would try the second one (with SaveAs instead of Save). As you use late binding, check values in excel vba object browser, and replace named constants with those values.

combo

 

[BJZeak]

So ... tried the suggestion in 928283 ... for whatever reason the following causes a 1004 error
wp.saveas FileName:=sFN, AccessMode:=xlShared, FileFormat:=xlExcel8
Note: neither xlShared nor xlExcel8 were defined in the Excel 2k3, suggesting to me that I may be missing a reference ... assigning values to these constants causes a 1004 error at run time.

Regardless of this error, someone suggested to me that I use a separate excel workbook for use as an update workbook ... then link the cells on the final workbook to this update workbook ... this avenue still has the same issue ... the only way I have found to update the final workbook is to close it and reopen it (which provides a dialog to update the worksheet) ... regardless of updating the worksheet directly or indirectly using a separate update workbook the current stumbling block is with finding a way to automatically refresh the active worksheet(s)

So ... the question comes full circle ... how does one automatically force updates in an excel worksheet in either SHARED or in data LINKED modes?

In Access if you make a change on a child form ... returning to a parent form just requires a REFRESH command to update child related changes ... basically looking for that kind of functionality in Excel 2003 format

F9 (calculate) was an expected solution but it doesn't appear to pull any changed data

 

[Andrzejek]

neither xlShared nor xlExcel8 were defined in the Excel 2k3, suggesting to me that I may be missing a reference

Since you are using the late binding of Excel object, you do not have (need) a reference to Excel object:
[tt]
Dim objApp As Object
Set objApp = CreateObject("Excel.Application")
[/tt]
Therefore the Excel's constants are not available to you, that's why the error on line:[tt]
wp.saveas FileName:=sFN, AccessMode:=[red]xlShared[/red], FileFormat:=[red]xlExcel8[/red][/tt]

>assigning values to these constants causes a 1004 error at run time.
What values have you used?

I would try:
[tt]wp.saveas FileName:=sFN, AccessMode:=[red]2[/red], FileFormat:=[red]56[/red][/tt]


---- Andy

There is a great need for a sarcasm font.

 

[BJZeak]

Thx Andy,

Access VBA wouldn't compile with the line by itself so this code didn't run at all without explicitly presetting the values
Had looked up the values and set them at the beginning of the update FUNCTION

function fnExcelUpdate(sFN as String) as boolean

dim xlShared as long
dim xlExcel8 as long
xlShared = 2
xlExcel8 = 56
.
.
.
wp.saveas FileName:=sFN, AccessMode:=xlShared, FileFormat:=xlExcel8
.
.
.

Which then resulted in the 1004 run time error ... I will try just using numbers in case the TYPE Long is possibly causing the 1004 issue

but as stated, regardless of using wp.saveas to a secondary workbook OR using wp.save to the actual shared workbook, both methods still have the same issue where I can't see the Access generated updates unless the workbook is closed (without saving) and then reopened. Doesn't Excel have some way to trigger a reload without having to close and reopen a workbook?

 

[combo]

Late binding does not support named arguments (FileName:= etc.), you need to pass arguments in default order.
I have no excel 2003, you may check if xlExcel8 constant is defined in this version too. If not, test excel version with xlApp.Version, use xlExcel8 for 2007+ (higher versions have xlWorkbookDefault=51 constant, if it exists in 2003 too, use 51 or 56 file format, depending on version).

In excel reloading workbook means saving it.

combo

 

[BJZeak]

Thx Combo ... guess that is the answer I was looking for ... so to sum up this thread

1) using an additional workbook doesn't work with this "SAVING IT" reload
both workbooks shared
workbook1 is updated from Access
workbook2 is linking cell data from Workbook1
SAVE does NOT refresh the linked cells

2) using only one workbook "SAVING IT" reloads the updated Shared Changes
workbook is updated directly from Access
cells do NOT update until clicking on Save

3) finally I found a setting in the 2003 EXCEL SHARED WORKBOOK dialog that automatically saves every 5 to n minutes
workbook Cells in this situation if set to 5 minutes will update within 5 minutes

so I am 86ing the 2 workbook approach and going back to the single shared workbook with an auto 5min update

Thanks everyone for your help with this