I have been using visual basic in Excel 2011 for Mac to open a file
and perform some operations on that file. The code to open the file is

Workbooks.Open Filename:= "Mac Backup:Data Files:Excel Files:Test
File.xlsx"

Mac Backup is the name of a removable drive.

The code has worked fine until I upgraded to Excel 2016 for Mac. Now
when I run the code I get the message "Sorry, we couldn't find Mac
Backup:Data Files:Excel Files:Test File.xlsx."

I have changed the colon to back slash and forward slash and still get
the error message. This same code works fine in Excel 2016 for PC
using the back slash. Apparently something has changed in the file
path structure in the Excel 2016 version for Mac. Any help would be
appreciated.

colglbo wrote:

I have been using visual basic in Excel 2011 for Mac to open a file
and perform some operations on that file. The code to open the file is

Workbooks.Open Filename:= "Mac Backup:Data Files:Excel Files:Test
File.xlsx"

Mac Backup is the name of a removable drive.

The code has worked fine until I upgraded to Excel 2016 for Mac. Now
when I run the code I get the message "Sorry, we couldn't find Mac
Backup:Data Files:Excel Files:Test File.xlsx."

I have changed the colon to back slash and forward slash and still get
the error message. This same code works fine in Excel 2016 for PC
using the back slash. Apparently something has changed in the file
path structure in the Excel 2016 version for Mac. Any help would be
appreciated.

Create a new macro-enabled (.xlsm) workbook in the same directory. Put this
code in a new module:

Sub whereAmI()
Cells(1, 1).Value = ActiveWorkbook.Path
End Sub

A1 now contains the path of the workbook. Update your existing code.

--
Are there people in your house right now you'd just love to strangle,
but can't because it's not polite to do that to company?

Sub whereAmI()
Cells(1, 1).Value = ActiveWorkbook.Path
End Sub


Just a minor point, but depending on context it's a very important point!

VBA 'best practice' suggests:

- use ActiveWorkbook only when the code is acting on or referencing a
workbook other than itself;

- use ThisWorkbook when code refs the workbook running the code.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

GS wrote:

Sub whereAmI()
Cells(1, 1).Value = ActiveWorkbook.Path
End Sub

Just a minor point, but depending on context it's a very important point!

VBA 'best practice' suggests:

- use ActiveWorkbook only when the code is acting on or referencing a
workbook other than itself;

- use ThisWorkbook when code refs the workbook running the code.

Good point.

--
Don't believe everything you think.

GS wrote:

Sub whereAmI()
Cells(1, 1).Value = ActiveWorkbook.Path
End Sub

Just a minor point, but depending on context it's a very important point!

VBA 'best practice' suggests:

- use ActiveWorkbook only when the code is acting on or referencing a
workbook other than itself;

- use ThisWorkbook when code refs the workbook running the code.

Good point.

I figured since you program in other languages that you'd pick up on the 'This'
part of an object ref-ing itself!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

GS wrote:

GS wrote:

Sub whereAmI()
Cells(1, 1).Value = ActiveWorkbook.Path
End Sub

Just a minor point, but depending on context it's a very important point!

VBA 'best practice' suggests:

- use ActiveWorkbook only when the code is acting on or referencing a
workbook other than itself;

- use ThisWorkbook when code refs the workbook running the code.

Good point.

I figured since you program in other languages that you'd pick up on the
'This' part of an object ref-ing itself!

I just put down the first thing that came to mind, and didn't really put any
thought into it. Since it's meant to be a one-time-only sorta thing, I'm not
terribly worried about it. (Also, I'm much more used to Activewhatever,
because my code is often run from the personal macro workbook rather than the
workbook being affected.)

--
Fear is overrated as a deterrent. Death works much better.

On 2018-03-11 16:27:29 +0000, colglbo said:

I have been using visual basic in Excel 2011 for Mac to open a file
and perform some operations on that file. The code to open the file is

Workbooks.Open Filename:= "Mac Backup:Data Files:Excel Files:Test
File.xlsx"

The difference between Excel 2011 and Excel 2016 for Mac is that you
must now write :

Workbooks.Open Filename:= "Mac Backup/Data Files/Excel Files/TestFile.xlsx"

On Mon, 12 Mar 2018 07:59:36 +0100, Patrick
wrote:

On 2018-03-11 16:27:29 +0000, colglbo said:

I have been using visual basic in Excel 2011 for Mac to open a file
and perform some operations on that file. The code to open the file is

Workbooks.Open Filename:= "Mac Backup:Data Files:Excel Files:Test
File.xlsx"

The difference between Excel 2011 and Excel 2016 for Mac is that you
must now write :

Workbooks.Open Filename:= "Mac Backup/Data Files/Excel Files/TestFile.xlsx"

Using the suggestion to find the path solved the problem. The required
path is

Workbooks.Open Filename:= "/Volumes/Mac Backup/Data Files/Excel
Files/TestFile.xlsx"

colglbo wrote:
I have been using visual basic in Excel 2011 for Mac to open a file
and perform some operations on that file. The code to open the file is

Workbooks.Open Filename:= "Mac Backup:Data Files:Excel Files:Test
File.xlsx"

Mac Backup is the name of a removable drive.

The code has worked fine until I upgraded to Excel 2016 for Mac. Now
when I run the code I get the message "Sorry, we couldn't find Mac
Backup:Data Files:Excel Files:Test File.xlsx."

I have changed the colon to back slash and forward slash and still get
the error message. This same code works fine in Excel 2016 for PC
using the back slash. Apparently something has changed in the file
path structure in the Excel 2016 version for Mac. Any help would be
appreciated.

Typical M$ garbage - remove useful functionality and say nothing.

They totally diddled file open/write as follows:
ActivePrinter = "Acrobat PDFWriter on FILE:"
' Above sets printer in Excel 2003; will crash in Excel 2010.
' In "modern" defective Excel apps, following prints to default as a
' file AND prompts file name; file in printer format, NOT in PDF format.
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=False, _
ActivePrinter:="Acrobat PDFWriter on FILE:", PrintToFile:=True, _
Collate:=False, PrToFilename:=pPath + vNam
Kill pPath + vNam 'only PDF left

colglbo wrote:
I have been using visual basic in Excel 2011 for Mac to open a file
and perform some operations on that file. The code to open the file is

Workbooks.Open Filename:= "Mac Backup:Data Files:Excel Files:Test
File.xlsx"

Mac Backup is the name of a removable drive.

The code has worked fine until I upgraded to Excel 2016 for Mac. Now
when I run the code I get the message "Sorry, we couldn't find Mac
Backup:Data Files:Excel Files:Test File.xlsx."

I have changed the colon to back slash and forward slash and still get
the error message. This same code works fine in Excel 2016 for PC
using the back slash. Apparently something has changed in the file
path structure in the Excel 2016 version for Mac. Any help would be
appreciated.

Typical M$ garbage - remove useful functionality and say nothing.

They totally diddled file open/write as follows:
ActivePrinter = "Acrobat PDFWriter on FILE:"
' Above sets printer in Excel 2003; will crash in Excel 2010.
' In "modern" defective Excel apps, following prints to default as a
' file AND prompts file name; file in printer format, NOT in PDF format.
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=False, _
ActivePrinter:="Acrobat PDFWriter on FILE:", PrintToFile:=True, _
Collate:=False, PrToFilename:=pPath + vNam
Kill pPath + vNam 'only PDF left

Note that VBA6 is used up to v2007; beginning with v2010 VBA7 is used for all
(x86/x64) editions of MSO.

FWIW
Also, beginning with v2007 Office you can generate PDFs directly using SaveAs
FixedFormat and choose XPS or PDF.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

GS wrote:
colglbo wrote:
I have been using visual basic in Excel 2011 for Mac to open a file
and perform some operations on that file. The code to open the file is

Workbooks.Open Filename:= "Mac Backup:Data Files:Excel Files:Test
File.xlsx"

Mac Backup is the name of a removable drive.

The code has worked fine until I upgraded to Excel 2016 for Mac. Now
when I run the code I get the message "Sorry, we couldn't find Mac
Backup:Data Files:Excel Files:Test File.xlsx."

I have changed the colon to back slash and forward slash and still get
the error message. This same code works fine in Excel 2016 for PC
using the back slash. Apparently something has changed in the file
path structure in the Excel 2016 version for Mac. Any help would be
appreciated.

Typical M$ garbage - remove useful functionality and say nothing.

They totally diddled file open/write as follows:
ActivePrinter = "Acrobat PDFWriter on FILE:"
' Above sets printer in Excel 2003; will crash in Excel 2010.
' In "modern" defective Excel apps, following prints to default as a
' file AND prompts file name; file in printer format, NOT in PDF
format.
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=False, _
ActivePrinter:="Acrobat PDFWriter on FILE:", PrintToFile:=True, _
Collate:=False, PrToFilename:=pPath + vNam
Kill pPath + vNam 'only PDF left

Note that VBA6 is used up to v2007; beginning with v2010 VBA7 is used
for all (x86/x64) editions of MSO.

FWIW
Also, beginning with v2007 Office you can generate PDFs directly using
SaveAs FixedFormat and choose XPS or PDF.

USEFUL info; Thanks.
Again, un-documented change(s) that wannabe users have to guess at.
M$ s SUCH a piece..

"Robert Baer" wrote in message

Note that VBA6 is used up to v2007; beginning with v2010 VBA7 is used
for all (x86/x64) editions of MSO.

FWIW
Also, beginning with v2007 Office you can generate PDFs directly using
SaveAs FixedFormat and choose XPS or PDF.

USEFUL info; Thanks.
Again, un-documented change(s) that wannabe users have to guess at.
M$ s SUCH a piece..

Not trying to defend MS but I don't follow why you regard the above as
'undocumented changes . . . users have to guess at'.

Before each new version the Excel team announce and discuss most planned
changes. On release a full list of changes, new features and any decremented
methods, is published on relevant MS sites and widely mirrored and discussed
elsewhere.

SaveAs PDF or XPS is hard to miss in Excel 2007 when you look at File /
SaveAs

The introduction of 64bit Office has been since its inception and continues
to be one of the most widely discussed and documented changes by MS and
elsewhere. That said if you only ever use 32bit Excel you don't need to know
anything about it. VBA7 can cater for a few special 64bit requirements
should you need them, mainly for use with APIs. Otherwise VBA7 is
effectively VBA6 even in 64bit Excel.

Peter T

Peter T wrote:
"Robert Baer" wrote in message

Note that VBA6 is used up to v2007; beginning with v2010 VBA7 is used
for all (x86/x64) editions of MSO.

FWIW
Also, beginning with v2007 Office you can generate PDFs directly using
SaveAs FixedFormat and choose XPS or PDF.

USEFUL info; Thanks.
Again, un-documented change(s) that wannabe users have to guess at.
M$ s SUCH a piece..

Not trying to defend MS but I don't follow why you regard the above as
'undocumented changes . . . users have to guess at'.

Before each new version the Excel team announce and discuss most planned
changes. On release a full list of changes, new features and any decremented
methods, is published on relevant MS sites and widely mirrored and discussed
elsewhere.

SaveAs PDF or XPS is hard to miss in Excel 2007 when you look at File /
SaveAs

The introduction of 64bit Office has been since its inception and continues
to be one of the most widely discussed and documented changes by MS and
elsewhere. That said if you only ever use 32bit Excel you don't need to know
anything about it. VBA7 can cater for a few special 64bit requirements
should you need them, mainly for use with APIs. Otherwise VBA7 is
effectively VBA6 even in 64bit Excel.

Peter T


Excuse me, have NEVER seen any info WRT Excel methods and/or
procedures. For all practical purposes "help" does not exist.

Furthermore, i gave code where one can PROGRAMATICALLY choose a
printer for the app, and then PROGRAMATICALLY print to it and give print
path if "ON FILE".

SaveAS etc is NOT an option when multiple instances (in a loop) ae
desired.

"Robert Baer" wrote in message
colglbo wrote:
I have been using visual basic in Excel 2011 for Mac to open a file
and perform some operations on that file. The code to open the file is

Workbooks.Open Filename:= "Mac Backup:Data Files:Excel Files:Test
File.xlsx"

Mac Backup is the name of a removable drive.

The code has worked fine until I upgraded to Excel 2016 for Mac. Now
when I run the code I get the message "Sorry, we couldn't find Mac
Backup:Data Files:Excel Files:Test File.xlsx."

I have changed the colon to back slash and forward slash and still get
the error message. This same code works fine in Excel 2016 for PC
using the back slash. Apparently something has changed in the file
path structure in the Excel 2016 version for Mac. Any help would be
appreciated.

Typical M$ garbage - remove useful functionality and say nothing.

Seems it was Apple that changed things -

https://macadmins.software/docs/UserContentIn2016.pdf

https://www.rondebruin.nl/mac/mac034.htm

There's a mountain of related links.

They totally diddled file open/write as follows:
ActivePrinter = "Acrobat PDFWriter on FILE:"
' Above sets printer in Excel 2003; will crash in Excel 2010.
' In "modern" defective Excel apps, following prints to default as a
' file AND prompts file name; file in printer format, NOT in PDF format.
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=False, _
ActivePrinter:="Acrobat PDFWriter on FILE:", PrintToFile:=True, _
Collate:=False, PrToFilename:=pPath + vNam
Kill pPath + vNam 'only PDF left

There could be several reasons. Here and when you posted your problem you
didn't give
any context. You were offered a possible solution but unless I miised it you
didn't reply.

Peter T

Peter T wrote:
"Robert Baer" wrote in message
colglbo wrote:
I have been using visual basic in Excel 2011 for Mac to open a file
and perform some operations on that file. The code to open the file is

Workbooks.Open Filename:= "Mac Backup:Data Files:Excel Files:Test
File.xlsx"

Mac Backup is the name of a removable drive.

The code has worked fine until I upgraded to Excel 2016 for Mac. Now
when I run the code I get the message "Sorry, we couldn't find Mac
Backup:Data Files:Excel Files:Test File.xlsx."

I have changed the colon to back slash and forward slash and still get
the error message. This same code works fine in Excel 2016 for PC
using the back slash. Apparently something has changed in the file
path structure in the Excel 2016 version for Mac. Any help would be
appreciated.

Typical M$ garbage - remove useful functionality and say nothing.

Seems it was Apple that changed things -

https://macadmins.software/docs/UserContentIn2016.pdf

https://www.rondebruin.nl/mac/mac034.htm

There's a mountain of related links.

They totally diddled file open/write as follows:
ActivePrinter = "Acrobat PDFWriter on FILE:"
' Above sets printer in Excel 2003; will crash in Excel 2010.
' In "modern" defective Excel apps, following prints to default as a
' file AND prompts file name; file in printer format, NOT in PDF format.
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=False, _
ActivePrinter:="Acrobat PDFWriter on FILE:", PrintToFile:=True, _
Collate:=False, PrToFilename:=pPath + vNam
Kill pPath + vNam 'only PDF left

There could be several reasons. Here and when you posted your problem you
didn't give
any context. You were offered a possible solution but unless I miised it you
didn't reply.

Peter T



So far, the ONLY solution that actually worked, was the one i posted,
and only in Excel 2003.
Certainly NOT in Excel 2010 that was purported to "do everything".