Set a reference to a Workbook w/out opening the Workbook?

R

ryguy7272

Does anyone know how to set a reference to a Workbook w/out opening
the Workbook?

I am experimenting with the code below:
SaveFile = Left(imgElement.Title, 27) '<-- This is a parsed string...
Dim sPathTemp As String
Dim BookName As String
Set xlApp = New Excel.Application
xlApp.Visible = True
'Set WB = xlApp.Workbooks.Open(sPath, , False) '< -- This opens the
file; I DON'T want to open the file
sPath = strpath & "/" & SaveFile
BookName = Mid(sPath, InStrRev(sPath, "/") + 1)
Set WB = Workbooks(Index:=BookName)

xlApp.Workbooks.Application.CalculateFull
Application.DisplayAlerts = False
buildsavedest = URL & SaveFile 'sPath
xlApp.Workbooks(SaveFile).SaveAs buildsavedest
xlApp.Workbooks(SaveFile).CheckIn SaveChanges:=True, _
Comments:=""

The code fails on this line:
xlApp.Workbooks(SaveFile).SaveAs buildsavedest

Error mssg is 'Subscript out of range'.

The files are in SharePoint. Any ideas on how to resolve this?
Thanks so much!!
 
R

ryguy7272

Does anyone know how to set a reference to a Workbook w/out opening
the Workbook?

I am experimenting with the code below:
SaveFile = Left(imgElement.Title, 27) '<-- This is a parsed string...
Dim sPathTemp As String
Dim BookName As String
Set xlApp = New Excel.Application
xlApp.Visible = True
'Set WB = xlApp.Workbooks.Open(sPath, , False) '< -- This opens the
file; I DON'T want to open the file
sPath = strpath & "/" & SaveFile
BookName = Mid(sPath, InStrRev(sPath, "/") + 1)
Set WB = Workbooks(Index:=BookName)

xlApp.Workbooks.Application.CalculateFull
Application.DisplayAlerts = False
buildsavedest = URL & SaveFile 'sPath
xlApp.Workbooks(SaveFile).SaveAs buildsavedest
xlApp.Workbooks(SaveFile).CheckIn SaveChanges:=True, _
      Comments:=""

The code fails on this line:
xlApp.Workbooks(SaveFile).SaveAs buildsavedest

Error mssg is 'Subscript out of range'.

The files are in SharePoint.  Any ideas on how to resolve this?
Thanks so much!!


I alse experimented with this:
Dim xlApp As Object
Set xlApp = New Excel.Application
xlApp.Visible = True
sPath = strpath & "/" & SaveFile
BookName = Mid(sPath, InStrRev(sPath, "/") + 1)
Set xlApp = GetObject(pathname:=sPath)
xlApp.Windows(BookName).Visible = True
xlApp.Workbooks.Application.CalculateFull
Application.DisplayAlerts = False
buildsavedest = URL & SaveFile 'sPath
xlApp.Workbooks(SaveFile).SaveAs buildsavedest
xlApp.Workbooks(SaveFile).CheckIn SaveChanges:=True, _
Comments:=""

This gives me an error on this line:
Set xlApp = GetObject(pathname:=sPath)

Erorr is: 'Automation error. Invalid syntax'
Perhaps 'GetObject' doesn't work with URLs... The files are stored
inSharePoint.

Any advice anyone??? Thanks!!!
 
J

Jim Cone

You cannot save a closed file.
You could open the file and use SaveAs to save it to another location.
Or you could use the FileCopy statement to copy the closed file to another drive.
The FileSystemObject can also be used to move/copy closed files.
How any of the above would go down in SharePoint, I don't know.

There are several SharePoint forum (web based) groups, see...
http://www.microsoft.com/office/community/en-us/default.mspx?CTT=3
or
http://www.aioe.org/ to access (the still alive) microsoft newsgroups.
--
Jim Cone
Portland, Oregon USA
http://tinyurl.com/ListFiles

..
..
..

"ryguy7272" <[email protected]>
wrote in message
Does anyone know how to set a reference to a Workbook w/out opening
the Workbook?

I am experimenting with the code below:
SaveFile = Left(imgElement.Title, 27) '<-- This is a parsed string...
Dim sPathTemp As String
Dim BookName As String
Set xlApp = New Excel.Application
xlApp.Visible = True
'Set WB = xlApp.Workbooks.Open(sPath, , False) '< -- This opens the
file; I DON'T want to open the file
sPath = strpath & "/" & SaveFile
BookName = Mid(sPath, InStrRev(sPath, "/") + 1)
Set WB = Workbooks(Index:=BookName)

xlApp.Workbooks.Application.CalculateFull
Application.DisplayAlerts = False
buildsavedest = URL & SaveFile 'sPath
xlApp.Workbooks(SaveFile).SaveAs buildsavedest
xlApp.Workbooks(SaveFile).CheckIn SaveChanges:=True, _
Comments:=""

The code fails on this line:
xlApp.Workbooks(SaveFile).SaveAs buildsavedest

Error mssg is 'Subscript out of range'.

The files are in SharePoint. Any ideas on how to resolve this?
Thanks so much!!
 
R

ryguy7272

You cannot save a closed file.
You could open the file and use SaveAs to save it to another location.
Or you could use the FileCopy statement to copy the closed file to another drive.
The FileSystemObject can also be used to move/copy closed files.
How any of the above would go down in SharePoint, I don't know.

There are several SharePoint forum (web based) groups, see...http://www.microsoft.com/office/community/en-us/default.mspx?CTT=3
orhttp://www.aioe.org/to access (the still alive) microsoft newsgroups.
--
Jim Cone
Portland, Oregon  USAhttp://tinyurl.com/ListFiles

.
.
.

"ryguy7272" <[email protected]>
wrote in messageDoes anyone know how to set a reference to a Workbook w/out opening
the Workbook?

I am experimenting with the code below:
SaveFile = Left(imgElement.Title, 27) '<-- This is a parsed string...
Dim sPathTemp As String
Dim BookName As String
Set xlApp = New Excel.Application
xlApp.Visible = True
'Set WB = xlApp.Workbooks.Open(sPath, , False) '< -- This opens the
file; I DON'T want to open the file
sPath = strpath & "/" & SaveFile
BookName = Mid(sPath, InStrRev(sPath, "/") + 1)
Set WB = Workbooks(Index:=BookName)

xlApp.Workbooks.Application.CalculateFull
Application.DisplayAlerts = False
buildsavedest = URL & SaveFile 'sPath
xlApp.Workbooks(SaveFile).SaveAs buildsavedest
xlApp.Workbooks(SaveFile).CheckIn SaveChanges:=True, _
      Comments:=""

The code fails on this line:
xlApp.Workbooks(SaveFile).SaveAs buildsavedest

Error mssg is 'Subscript out of range'.

The files are in SharePoint.  Any ideas on how to resolve this?
Thanks so much!!


But the file is open; I just want to reference the open file, then
save it. This is going to require some hard-cord out-of-the-box-
thinking.
Thanks Jim!!!
 
R

ryguy7272

But the file is open; I just want to reference the open file, then
save it.  This is going to require some hard-cord out-of-the-box-
thinking.
Thanks Jim!!!

Got it working!! I had some help from a colleague in my office.
Thanks Mandeep!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top