For the first time since I switched to Microsoft Excel for Mac 2011, I came across the need to process a number of workbooks all located in the same folder. Having done this many times in a Windows environment I wasn’t expecting much difficulty.
I've been periodically using the Excel 2016 for Mac updates but quickly abandoned them because my sheets rely so heavily on VBA and support for that seemed to be missing. Skip to main content. Visual Basic Support in Mac 2016 Hi. Excel VBA and Text Files. Open a Text File; Writing to a text file; Excel VBA and User Forms. 5 part section >> An Excel Picture Viewer Project. 12 part section >> Excel VBA and Charts. 4 part section >> A TreeView Project. A 4 part section >> > BUY THE BOOK OF THIS COURSE; Writing to text files in Excel VBA. In the previous lesson, you saw how to open up a text file with Excel VBA code. In this lesson, you'll learn how to write data from a spreadsheet to a text file.
![Excel For Mac Write Vba Excel For Mac Write Vba](/uploads/1/2/5/4/125446209/925728992.jpg)
But as usual, there were a few subtle differences in the way the VBA macro needed to be written for the Mac. My first issue came with accessing files and folders for the Mac file system. In the Windows world I would do something like this: strPath = “C: Data excel ” I realized that wasn’t going to work. After some research I found that the way to reference the Mac filesystem in VBA is like this: strPath = “Macintosh HD:Users:username:Documents:TestExcel:” Once I found that I thought I’d be sailing along. But then I ran into my next hurdle. Excel for Mac 2011 doesn’t handle wildcards in VBA.
Previously I would handle getting filenames like this: strFileName = dir(“c: data excel.xls”) In the Mac VBA world it needs to be handled like this: strFileName = Dir(“”) Once I had those speed bumps out of the way it was clear sailing. Below is an example of the finished code used to process all the XLSX files in a directory. Sub processdirectoryofworkbooks Application.ScreenUpdating = False Dim wbOpen As Workbook Const strPath As String = 'Macintosh HD:Users:username:Documents:TestExcel:' Dim strFileName As String ChDir strPath 'Get FileName strFileName = Dir(') Do While strFileName ' Set wbOpen = Workbooks.Open(strPath & strFileName) varName = varName & vbCrLf & ActiveWorkbook.Name 'Process Workbooks in any way needed wbOpen.Close 0 'Get next file in folder strFileName = Dir Loop MsgBox varName & vbCrLf & 'Done' End Sub.
I have a subroutine which writes a text to a.txt file in VBA: Sub WriteToFile(fileName as String, content as String) Open fileName For Output As #1 Print #1, content Close #1 End Sub This subroutine works well under Windows. However, I realize that under Mac, for any targeFile which has more than 29 characters, an error Bad file name or number will be raised on the line Open fileName For Output As #1. But I do need to write to a lot of files whose name is long.
Actually, this issue is quite common. For instance, also mentions that There are problems with long file names when you use Dir on a Mac, 27/28 characters (without the ext) is the maximum(with the extension this is 32 characters). It seems that AppleScript may be a solution for this. But as I am looking for a workaround for writing instead of Dir, I have not found a precise solution for writing. Does anyone know how to achieve this?
This is a more robust version of an earlier, since-deleted answer. Unlike the deleted answer, which used do shell script, this answer uses only AppleScript features in the AppleScript command string, and also correctly escapes the arguments that get embedded in the command string. Try the following VBA procedure, which synthesizes an AppleScript command string for writing to a file and executes it with MacScript. ' - Specify the output filename either as a mere filename - ' in which case the file is created in the active workbook's folder - ' or as a HFS path (with ':' as the path separator).
![Excel For Mac Write Vba Excel For Mac Write Vba](/uploads/1/2/5/4/125446209/700700816.png)
' - Note that line breaks in `content` are written as defined, ' and that on a Mac `vbNewLine` is chr(13) (CR r) only. ' If you want Unix-style LF ( n) line breaks instead, use chr(10) explicitly. ' Example: ' WriteToFile 'test.txt', 'Hello, world.' & Chr(10) Sub WriteToFile(fileName As String, content As String) Dim filePath, filePathEscaped, contentEscaped, asCmd ' If the filename contains no path component, default to the active workbook's path. If InStr(fileName, ':') = 0 Then filePath = ActiveWorkbook.Path & ':' & fileName Else filePath = fileName End If ' In case the file path or input string contain double quotes, we must ' -escape them for inclusing in the AppleScript command string first. FilePathEscaped = Replace(filePath, ', ' ') contentEscaped = Replace(content, ', ' ') ' Synthesize the AppleScript command string.
AsCmd = 'set fRef to open for access ' & filePathEscaped & ' with write permission' & Chr(13) & 'set eof fRef to 0' & Chr(13) & 'write ' & contentEscaped & ' to fRef' & Chr(13) & 'close access fRef' ' Execute the AppleScript command string. ' Any error will generically be reported as 'Invalid procedure call or ' argument', unfortunately. MacScript asCmd End Sub. An inelegant and unsatisfying (but easy) alternative is to. Write the output to a reserved filename that's short enough not to cause the problem. After the writing is complete and the file is closed, use FileCopy to copy that short-named file to the long name you actually want. Delete the short-named file It turns out that FileCopy isn't so finicky about filename length.
You'd probably want to encase it all in checks to make sure that the reserved name doesn't exist before you write, and to make sure that you delete the file with the reserved name if any kind of hitch develops while you have it open. Or you can be open with the user about what you're doing and query the user for both names at the outset.