VBA - Export Sheet to Tab Delimited Text (Mac Office 365) - Read Only Error

[bravia118]

Hi all,

Longtime viewer and first time poster. I'm trying very simply to use a VBA macro to;

- Copy a sheet to a new workbook
- Save As the sheet to be 'ExExport.txt' in tab delimited format (in the current folder that the original spreadsheet was in)
- Close the workbook

I feel like I'm on the right track, but I have exhausted my google searching and forum trawling skills so I thought I'd ask. I should also point out that I am running this on a Mac in office 365 v16.

Please Login or Register  to view this content.

This current script runs up the error 'Run-time error '1004'".Cannot access read-only document "exexport.txt".

If anyone is able to offer any advice it would be greatly appreciated.

TIA Dan

[Marc L]

Hi,

check first if the text file already exists, try with another file name …

[bravia118]

Tried multiple times with various file names still to no avail. Regardless if the file exists or not I would at least like a prompt to overwrite?

Does anyone have any idea what I'm missing? Is there something specific to the Mac version of office 365, the macro I used to use doesn't work in this package either?

[CK76]

Have a read of...
https://answers.microsoft.com/en-us/...184c67b?page=1

And...
http://www.rondebruin.nl/mac/mac034.htm

Oh and make sure that you have latest update to Office 365 applied.

It's to do with Apple's sandbox requirement. Which VBA doesn't handle well on it's own (since VBA hasn't been updated for ages).

[bravia118]

Thanks CK76!

Working through that helped a great deal! I've managed to get the active sheet to copy and store into a folder as a text document, however I can't seem to make it tab delimited.

I believe the filetype is xlText, that CBA only seems to allow for file type numbers. I had a look on docs.microsoft.com/en-gb/office/vba/api/Excel.XlFileFormat - and have tried most of them. Can't seem to find how to make that happen?

Please Login or Register  to view this content.

Thanks for your help guys!

[CK76]

Not sure on that one as I don't use Mac all that much.

May be try 19, which is xlTextMac... though I have my doubts.