Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to reference a file in a different instance of Excel?
I’m trying to save a file to SharePoint. This code works fine when
there are two Workbooks in the same instance of Excel: SaveFile = Left(imgElement.Title, 27) MsgBox "Saving changes to " & SaveFile & " and checking this file into SharePoint now!!", vbSystemModal Set WB = Workbooks(SaveFile) WB.Activate WB.CheckIn SaveChanges:=True, Comments:="" WB.Close When I have two instances of Excel, how can I reference the OTHER Workbook in the OTHER instance of Excel? I’m trying something like this: SaveFile = Left(imgElement.Title, 27) MsgBox "Saving changes to " & SaveFile & " and checking this file into SharePoint now!!", vbSystemModal Dim savepath As String savepath = strPath & "/" & SaveFile Dim GetActiveWB As String GetActiveWB = ActiveWorkbook.path & "/" & SaveFile Set wb = Workbooks(GetActiveWB) wb.Activate wb.CheckIn SaveChanges:=True, Comments:="" wb.Close Now, I’m getting a ‘subscript out of range’ message right he Set wb = Workbooks(GetActiveWB) How can I reference this Workbook (variable is ‘SaveFile’) and save it to SharePoint, from a different INSTANCE of Excel? Thanks!! Ryan--- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to reference a file in a different instance of Excel?
On Jul 28, 3:45*pm, ryguy7272 wrote:
I’m trying to save a file to SharePoint. *This code works fine when there are two Workbooks in the same instance of Excel: SaveFile = Left(imgElement.Title, 27) MsgBox "Saving changes to " & SaveFile & " and checking this file into SharePoint now!!", vbSystemModal Set WB = Workbooks(SaveFile) WB.Activate WB.CheckIn SaveChanges:=True, Comments:="" WB.Close When I have two instances of Excel, how can I reference the OTHER Workbook in the OTHER instance of Excel? I’m trying something like this: SaveFile = Left(imgElement.Title, 27) MsgBox "Saving changes to " & SaveFile & " and checking this file into SharePoint now!!", vbSystemModal Dim savepath As String savepath = strPath & "/" & SaveFile Dim GetActiveWB As String GetActiveWB = ActiveWorkbook.path & "/" & SaveFile Set wb = Workbooks(GetActiveWB) wb.Activate wb.CheckIn SaveChanges:=True, Comments:="" wb.Close Now, I’m getting a ‘subscript out of range’ message right he Set wb = Workbooks(GetActiveWB) How can I reference this Workbook (variable is ‘SaveFile’) and save it to SharePoint, from a different INSTANCE of Excel? Thanks!! Ryan--- I’m trying to reference a file that is open, but not active. What is wrong with thisa methodology? SaveFile = Left(imgElement.Title, 27) Fname = strPath & "/" & SaveFile BookName = Mid(Fname, InStrRev(Fname, "/") + 1) Set wb = Workbooks(Index:=BookName) wb.Activate wb.CheckIn SaveChanges:=True, Comments:="" wb.Close Code fails on this line: Set wb = Workbooks(Index:=BookName) Error mssg is ‘subscript out of range’ Any thoughts? Thanks!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to reference a file in a different instance of Excel?
Filename contains extension
BookName = Mid(Fname, InStrRev(Fname, "/") + 1) In the above line Mid returns from last "/" to end of Fname. Suggestion: BookName = Mid(Fname, InStrRev(Fname, "/") + 1,InStr(Fname,".xls")- InStrRev(Fname, "/") -1) Pls confirm. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to reference a file in a different instance of Excel?
On Jul 29, 2:32*am, Javed wrote:
Filename contains extension BookName = Mid(Fname, InStrRev(Fname, "/") + 1) In the above line Mid returns from last "/" to end of Fname. Suggestion: BookName = Mid(Fname, InStrRev(Fname, "/") + 1,InStr(Fname,".xls")- InStrRev(Fname, "/") -1) Pls confirm. Nope, that gives me the file name, but without the ".xls". So, i simply concatenated a ".xls" onto the end. Now, I have the file name and the extension, but it STILL WON'T SAVE. I can't figure it out!! Error is still on this line: Set wb = Workbooks(Index:=BookName) Error mssg is 'subscript out of range'. Any thoughts? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to reference a file in a different instance of Excel?
On Jul 29, 6:40*am, ryguy7272 wrote:
On Jul 29, 2:32*am, Javed wrote: Filename contains extension BookName = Mid(Fname, InStrRev(Fname, "/") + 1) In the above line Mid returns from last "/" to end of Fname. Suggestion: BookName = Mid(Fname, InStrRev(Fname, "/") + 1,InStr(Fname,".xls")- InStrRev(Fname, "/") -1) Pls confirm. Nope, that gives me the file name, but without the ".xls". *So, i simply concatenated a ".xls" onto the end. *Now, I have the file name and the extension, but it STILL WON'T SAVE. *I can't figure it out!! Error is still on this line: Set wb = Workbooks(Index:=BookName) Error mssg is 'subscript out of range'. Any thoughts? 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 | |||
Get reference to a file opened in another instance of XL | Excel Programming | |||
Separate Window/Instance Per Excel File | Excel Discussion (Misc queries) | |||
Force a File to Open in a New Excel Instance | Excel Programming | |||
How to open a new instance of EXCEL and .xls file | Excel Worksheet Functions | |||
Set up Excel to start new instance for each new file opened. | Excel Discussion (Misc queries) |