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---

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!!

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.

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?

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!!