VBA write to Sharepoint Issue

[rfuschjr]

First, let me stae I know nothing about Sharepoint other than I now have to place all the reports I produce in Sharepoint folders.

Second, let me state the we produce about 75 reports per month out of a variety of MsAccess data bases. Most involve running a number of complex queries which result in one final 'report' table. An Excel spreadsheet has been linked to this table and is refreshed and was written to a share. All this was fully automated via an Access form containing a button.

Press the report button and just walk away. The queries would be run via vba behind the button. The same vba would open the Excel 'template' (as we call it), refresh all pivots there in (previously linked to the Acess resultant table) and the Excel file written to is appropriate folder/subfolder on the share.

It works flawlessly like a champ. We even took it a step further and scheduled some of these things via task scheduler...its the only too set I have.

We will not be migrating from Access any time soon and that ok with me.

So they go out and bring in a Sharepoint guy who sells them on this structure stuff, ease of finding reports etc etc. I admit things 'look' more organized and the site makes life easier for the end user. In theory the only part of my process to change would be the path to where I save my Excel reports.

When I manually save them from Excel to the new path I never have a problem. When my vba issues its 'save' command, it sometimes works, sometimes errors out. Best I can tell its taking way long to save the file and perhaps loosing it connection or something. Anyway my vba errors out on the SAVE command. I click continue and it usually will continue...eventually...sometimes I click continue twice before it saves.

Its very frustarting and there are no experts here to help out.

Here is essentially how are stuff is run:
.
.
.
TemplateBook = "G:\ReportTemplates\CLINIC_Rpt.xls"
NewBook = "\\myreports\groups\Clinic\CLINIC_Rpt.xls"
DoCmd.OpenQuery "qryCLINIC1", acViewNormal
DoCmd.OpenQuery "qryCLINIC2", acViewNormal
DoCmd.OpenQuery "qryCLINIC3", acViewNormal
RefreshSheets
.
.
.
Function RefreshSheets()
Dim xcelapp As Object
Dim pc As Object
Dim wks As Object
Dim qt As Object

Set xcelapp = CreateObject("Excel.Application")
xcelapp.Workbooks.Open TemplateBook
xcelapp.DisplayAlerts = False

'Refresh All Workbook Sheets
xcelapp.ActiveWorkbook.RefreshAll

'Force a pause here
Call Timr(10) 'waits 10 secs

'Disable Users Ability to refresh pivots in workbook
For Each pc In xcelapp.ActiveWorkbook.PivotCaches
pc.EnableRefresh = False
Next pc

'Disable Users Ability to refresh worksheets in workbook
For Each wks In xcelapp.ActiveWorkbook.Worksheets
For Each qt In wks.QueryTables
qt.EnableRefresh = False
Next qt
Next wks

'Save to appropriate location
xcelapp.ActiveWorkbook.SaveAs Filename:=NewBook
xcelapp.ActiveWorkbook.Close
Set xcelapp = Nothing 'nessecary to prevent hanging Excelprocesses on your machine
End Function
Function Timr(PauseTime As Variant)
' Wait PauseTime secs
Dim Start, Finish, TotalTime
If True Then
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' <===== Key statement: Yield to other processes.
Loop
Finish = Timer ' Set end time.
TotalTime = Finish - Start ' Calculate total time.
End If




Like I said, at times it hangs here:
xcelapp.ActiveWorkbook.SaveAs Filename:=NewBook

with an error saying the file is already in use or something....I click the green arrow to continue and it actually usually saves the file and continues on.

I NEVER EVER NEVER have this issue with a straight save to a share...only with the goofy path syntax I was told to use for Sharepoint.

I do not know if my vba is incompatible or if the Sharepoint location 'lives' in an area that causes the time out etc etc.

ANY HELP IS APPRECIATED!!!!!!!!!!!!!!

 

[Frink]

Hallo,

Have you tried forcibly deleting 'NewBook' before calling the .SaveAs?

Might help as the error you report is that it's in use.

- Frink

 

[rfuschjr]

I can try that!