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

    Joined:
    Jul 12, 2013
    #1
    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.
    _________
    Code:
    
    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
        ' {""com.microsoft.Excel.xls""} to
        ' {""com.microsoft.excel.xls"",""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 " & _
                 " {""com.microsoft.Excel.xls"", "".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
                Else
                    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

    glenthompson

    Joined:
    Apr 27, 2011
    Location:
    Virginia
    #2
    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