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

  • Thread starter Thread starter ryguy7272
  • Start date Start date
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!!
 
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!!
Click to expand...


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!!!
 
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!!
 
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!!
Click to expand...


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!!!
 
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!!!
Click to expand...

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