none
Excel for Mac 2011: Macro to split every 1000 lines and export to txt file RRS feed

  • Question

  • just to preface everything, I'm a total vb hack - as in cut/copy/paste and try to make things work. I had a working version of the script below on my windows machine, but now I'm trying to get it working in Excel 2011 for Mac. I get a path/access error. Could someone please help me figure out what I'm doing wrong? Thanks in advance!

    Public Sub a_SaveAsTextWithDelimiter()
    Const MYFILE = "Macintosh HD/Users/darrenmason/Documents/Products/Creator NXT/Serials/"
    Dim Last_Column As Integer
    Dim Last_Row As Long
    Dim FileNum As Integer
    Dim My_Range As Range
    Dim My_Cell As Variant
    
    FileNum = FreeFile
    
    With ActiveSheet.Cells
        Last_Column = .Find("*", [A1], , , xlByColumns, xlPrevious).Column
        Last_Row = .Find("*", [A1], , , xlByRows, xlPrevious).Row
    End With
    
    Set My_Range = ActiveSheet.Range("A1:A" & Last_Row)
    
    Open MYFILE & "C2NXT_STD.txt" For Output As #FileNum
    For Each My_Cell In My_Range
        If My_Cell.Row Mod 1000 = 0 Then
            Close #FileNum
            Open MYFILE & "C2NXT_STD_" & (My_Cell.Row \ 1000) & "_" & (Format(Date, "yyyymmdd")) & ".txt" For Output As #FileNum
        End If
        Print #FileNum, My_Cell.Value
    Next
    Close #FileNum
    End Sub
    Thursday, August 23, 2012 4:41 AM

Answers

  • I am not sure whether in Mac the features supported but you can try.....

    I am just trying give a stepline and hope you can carry forward to your exact solution....

    Public Sub a_SaveAsTextWithDelimiter()
    Const MYFILE = "Macintosh HD/Users/darrenmason/Documents/Products/Creator NXT/Serials/"
    Const RowToSplit = 5
    Dim Last_Row As Long
    Dim FileNum As Integer
    Dim Data As Variant
    Dim i As Long
    Dim lStartRow As Long
    FileNum = FreeFile
    Last_Row = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
    For i = RowToSplit To Last_Row Step RowToSplit
            Data = WorksheetFunction.Transpose(Range("a" & i & ":A" & i - 4).Value)
            Open MYFILE & "C2NXT_STD_" & (i \ RowToSplit) & "_" & (Format(Date, "yyyymmdd")) _
                & ".txt" For Output As #FileNum
            Print #FileNum, Join(Data, vbCrLf)
            Close #FileNum
    Next i
    End Sub


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Thursday, August 23, 2012 7:36 AM
    Answerer
  • The file path separator on the Mac is the colon : not slash /

    Excel MVP Rob DeBruin has a web site that has examples of using file paths on a Mac.

    VBA code in Excel 2011 for the Mac


    Mac MVP

    Friday, August 24, 2012 11:57 AM

All replies

  • I am not sure whether in Mac the features supported but you can try.....

    I am just trying give a stepline and hope you can carry forward to your exact solution....

    Public Sub a_SaveAsTextWithDelimiter()
    Const MYFILE = "Macintosh HD/Users/darrenmason/Documents/Products/Creator NXT/Serials/"
    Const RowToSplit = 5
    Dim Last_Row As Long
    Dim FileNum As Integer
    Dim Data As Variant
    Dim i As Long
    Dim lStartRow As Long
    FileNum = FreeFile
    Last_Row = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
    For i = RowToSplit To Last_Row Step RowToSplit
            Data = WorksheetFunction.Transpose(Range("a" & i & ":A" & i - 4).Value)
            Open MYFILE & "C2NXT_STD_" & (i \ RowToSplit) & "_" & (Format(Date, "yyyymmdd")) _
                & ".txt" For Output As #FileNum
            Print #FileNum, Join(Data, vbCrLf)
            Close #FileNum
    Next i
    End Sub


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Thursday, August 23, 2012 7:36 AM
    Answerer
  • Thank you Asadulla. This is helpful because its producing the same exact error I'm receiving. It seems I have a permissions or folder path error in line 2. Thoughts anyone? Can someone confirm this would be the proper format for folder path on a Mac in VB? What about the spaces between Macintosh HD and Creator NXT?
    Thursday, August 23, 2012 4:16 PM
  • The file path separator on the Mac is the colon : not slash /

    Excel MVP Rob DeBruin has a web site that has examples of using file paths on a Mac.

    VBA code in Excel 2011 for the Mac


    Mac MVP

    Friday, August 24, 2012 11:57 AM