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.

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

Yeah, nothing 'wrong' with that; -my stuff in PERSONAL.xls is configured that
way too! Given the scenario it's perfectly acceptable. Different story, though,
if the workbook was saved hidden, or was an addin, or Excel had one of its
common burps during the load process.

Are you aware that if the file gets corrupted the Workbook_Open event may not
even fire? I stopped using it many ions ago and replaced the open/beforeclose
events with the autorun macros Auto_Open and Auto_Close and redirect from
there.

FWIW
I've just got into the habit of following best practice rules in a more strict
way than most VBAers, more so now due to the strictness in programming in C#
more recently, but mostly because I'm also a long-time dedicated follower of
the "Professional Excel Development" principles which also strongly advocate
following best practice rules.

Your helpful contributions here are appreciated; -I hope you keep following
thess Excel NG

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

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

Yeah, nothing 'wrong' with that; -my stuff in PERSONAL.xls is configured
that way too! Given the scenario it's perfectly acceptable. Different
story, though, if the workbook was saved hidden, or was an addin, or
Excel had one of its common burps during the load process.

Are you aware that if the file gets corrupted the Workbook_Open event
may not even fire? I stopped using it many ions ago and replaced the
open/beforeclose events with the autorun macros Auto_Open and Auto_Close
and redirect from there.

New one on me. I don't have much that happens in _Open, just something that
finds which columns hold which data and sets some globals -- but believe me,
I'd notice PDQ if that macro didn't fire.

FWIW
I've just got into the habit of following best practice rules in a more
strict way than most VBAers, more so now due to the strictness in
programming in C# more recently, but mostly because I'm also a long-time
dedicated follower of the "Professional Excel Development" principles
which also strongly advocate following best practice rules.

On the one hand, I try to do "best practices" when and where feasible (if I
am aware of said best practices), but on the other hand, I'm not a
"professional". Shrug.

Your helpful contributions here are appreciated; -I hope you keep
following thess Excel NG

I've been posting here since 2003; the odds of me leaving are pretty slim.

--
the perfect mix of cute and psycho

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"

I've been posting here since 2003; the odds of me leaving are pretty slim.

That's much longer than me! I followed the Classic VB group here back when MS
scratched the public forums; -don't recall when that was exactly. I've been
using Excel since v4; -migrating from Lotus. Started into VBA in 2003 and fell
under the mentorship of one of the authors of the Excel <? VBA - Programmer to
Programmer book series. I was asked to do a review for their 1st edition of Pro
Excel Development and this is where I adopted the best practice principles. I
migrated to VB6 in 2004; -still consider myself a newbie due to no formal
training in computers or programming. I did a stint in the school of hard
knocks in both VBA/VB6, though, which I figure counts for something. These
forums are a life saver to say the least!

Thanks for sharing...

--
Garry

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

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"

"GS" wrote in message
I've been posting here since 2003; the odds of me leaving are pretty
slim.

under the mentorship of one of the authors of the Excel <? VBA -
Programmer to Programmer book series. I was asked to do a review for their
1st edition of Pro Excel Development

Well well, it's still there on Amazon, I got a similar request;)

I came here when the 'original' CompuServe forums closed, also where the PED
authors hung out.

Sadly the 'new' CompuServe forums packed up a few months ago.

Peter T

"GS" wrote in message
I've been posting here since 2003; the odds of me leaving are pretty slim.

under the mentorship of one of the authors of the Excel <? VBA -
Programmer to Programmer book series. I was asked to do a review for their
1st edition of Pro Excel Development

Well well, it's still there on Amazon, I got a similar request;)

I came here when the 'original' CompuServe forums closed, also where the PED
authors hung out.

Sadly the 'new' CompuServe forums packed up a few months ago.

Peter T

Yeah, there's a 2nd edition now, and I suspect it's the last one for awhile
because some of the original authors aren't available just now. (not confirmed)

--
Garry

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

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.

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

That can be fully automated!!

--
Garry

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

"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

"Robert Baer" wrote in message
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.

VBA and the Excel object model are exhaustively documented. For help in the
IDE just select a keyword and press F1, typically it'll take you to what
you're looking for. Look through 'Object Browser", and F1 anything for
further help. Plenty on-line too, eg this for Excel 2010 and similar for
other versions.

https://msdn.microsoft.com/en-us/lib...ffice.14).aspx

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

I don't follow if you mean something that didn't work for you or a solution
for others, or why it's relevant to anything I mentioned.

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

Why not...?

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