| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set a reference to a Workbook w/out opening the Workbook?
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!! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Set a reference to a Workbook w/out opening the Workbook?
On Jul 26, 12:19*am, ryguy7272 wrote:
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!!! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Set a reference to a Workbook w/out opening the Workbook?
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/comm...ult.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" 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!! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Set a reference to a Workbook w/out opening the Workbook?
On Jul 26, 10:37*am, "Jim Cone" wrote:
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/comm...ult.mspx?CTT=3 orhttp://www.aioe.org/to access (the still alive) microsoft newsgroups. -- Jim Cone Portland, Oregon *USAhttp://tinyurl.com/ListFiles . . . "ryguy7272" wrote in ... 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!! 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!!! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Set a reference to a Workbook w/out opening the Workbook?
On Jul 26, 8:13*am, ryguy7272 wrote:
On Jul 26, 10:37*am, "Jim Cone" wrote: 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/com...ult.mspx?CTT=3 orhttp://www.aioe.org/toaccess (the still alive) microsoft newsgroups. -- Jim Cone Portland, Oregon *USAhttp://tinyurl.com/ListFiles . . . "ryguy7272" wrote in ... 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!! 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!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| lost reference to activesheet when opening workbook inside IE | Excel Programming | |||
| when opening an Excel Workbook, another blank workbook also opens | Excel Discussion (Misc queries) | |||
| How to make the opening of a workbook conditional upon the opening of another workbook | Excel Programming | |||
| How to make opening of workbook conditional of opening of another workbook | Excel Programming | |||
| Question for Experts: Opening workbook with workbook references | Excel Programming | |||
![ryguy7272[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
