Need VBA macro to select and import .txt file

Discussion in 'Mac Apps and Mac App Store' started by brunwynne, Jul 12, 2013.

  1. brunwynne, Jul 12, 2013
    Last edited by a moderator: Jul 13, 2013

    brunwynne macrumors newbie

    Jul 12, 2013
    Good evening,

    I apologize if this is not the correct sub-category for seeking VBA macro help.

    I am working on a project that requires me to have a macro that prompts the user to select a .txt file from the computer, and import the selected file into my workbook for use.

    There is tons of helpful stuff for windows! but almost nothing for Mac.

    I have this one code here, but the filters on it, prevent the user from selecting anything other then an excel file.

    I also need the file to be imported into my template workbook, not open a whole new one.

    Here is the only code I found so far, but I need more help. I am super frustrated.
    Sub Select_File_Or_Files_Mac()
        Dim MyPath As String
        Dim MyScript As String
        Dim MyFiles As String
        Dim MySplit As Variant
        Dim N As Long
        Dim Fname As String
        Dim mybook As Workbook
        On Error Resume Next
        MyPath = MacScript("return (path to documents folder) as String")
        'Or use MyPath = "Macintosh HD:Users:Ron:Desktop:TestFolder:"
        ' In the following statement, change true to false in the line "multiple
        ' selections allowed true" if you do not want to be able to select more
        ' than one file. Additionally, if you want to filter for multiple files, change
        ' {""""} to
        ' {"""",""public.comma-separated-values-text""}
        ' if you want to filter on xls and csv files, for example.
        MyScript = _
        "set applescript's text item delimiters to "","" " & vbNewLine & _
                   "set theFiles to (choose file of type " & _
                 " {"""", "".txt""} " & _
                   "with prompt ""Please select a file or files"" default location alias """ & _
                   MyPath & """ multiple selections allowed false) as string" & vbNewLine & _
                   "set applescript's text item delimiters to """" " & vbNewLine & _
                   "return theFiles"
        MyFiles = MacScript(MyScript)
        On Error GoTo 0
        If MyFiles <> "" Then
            With Application
                .ScreenUpdating = False
                .EnableEvents = False
            End With
            MySplit = Split(MyFiles, ",")
            For N = LBound(MySplit) To UBound(MySplit)
                ' Get the file name only and test to see if it is open.
                Fname = Right(MySplit(N), Len(MySplit(N)) - InStrRev(MySplit(N), Application.PathSeparator, , 1))
                If bIsBookOpen(Fname) = False Then
                    Set mybook = Nothing
                    On Error Resume Next
                    Set mybook = Workbooks.Open(MySplit(N))
                    On Error GoTo 0
                    If Not mybook Is Nothing Then
                        MsgBox "You open this file : " & MySplit(N) & vbNewLine & _
                               "And after you press OK it will be closed" & vbNewLine & _
                               "without saving, replace this line with your own code."
                        mybook.Close SaveChanges:=False
                    End If
                    MsgBox "We skipped this file : " & MySplit(N) & " because it Is already open."
                End If
            Next N
            With Application
                .ScreenUpdating = True
                .EnableEvents = True
            End With
        End If
    End Sub
    Function bIsBookOpen(ByRef szBookName As String) As Boolean
    ' Contributed by Rob Bovey
        On Error Resume Next
        bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
    End Function
  2. glenthompson macrumors 68000


    Apr 27, 2011
    Easiest approach to something like this is to turn on record macro then go through the steps you would take to do the job. Then take the recorded macro and tweak it to your requirements.

Share This Page