Selecting multiple files in MacOS VBA and adding the data to main workbook

N

Nelsini

New Member
Joined
May 13, 2021
Messages
25
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
Hello!
We're currently migrating from W10 to MacOS in my company and I'm trying to convert some VBA scripts to be MacOS compatible.
We have this macro in W10 where we gather data depending on errors in columns from several csv files and place it in the main workbook, where it then get filtered by a different macro:

VBA Code: 
Sub Splunk_import()

'String definition
Dim Datei As String
Dim letzteZeileNachImport As String
Dim letztezeile As String
Dim Dateiname As String
Const E109 As String = "_ERROR109_"
Const E207 As String = "_ERROR207_"
Const E1302 As String = "_ERROR1302_"
Const resetting As String = "_resetting_"
Const RDT As String = "RDT"
Const ETDR As String = "ETDR"
Const TSENF As String = "TSENF"
Const RAJP As String = "RAJP"
Dim ChDir As String
'TBD
Application.ScreenUpdating = False

'Here we determine the last row of column A
letztezeile = ActiveSheet.Cells(1048576, 1).End(xlUp).Row
'letzteZeilePlusEins = letztezeile + 1

'UserId
UserName = VBA.Environ("Username")
'Path preset for the dialog box
ChDrive "C:\"
ChDir = ActiveWorkbook.path
'File type is limited to csv
Datei = Application.GetOpenFilename("csv Files (*.csv), *.csv")
'Filename is extracted from path
Dateiname = Right(Datei, Len(Datei) - 25) '' to have only the file name without path the 25 must be adjusted for another PFad
'Delimiter preset
Workbooks.OpenText Datei, comma:=True
ActiveSheet.UsedRange.Copy _
Destination:=ThisWorkbook.ActiveSheet.Range("C" & letztezeile + 1)
ActiveWorkbook.Close False
Application.ScreenUpdating = True

'Here we determine the last row of column A after importing
letzteZeileNachImport = ActiveSheet.Cells(1048576, 1).End(xlUp).Row

'The error code is determined and entered depending on the open file
If InStr(Dateiname, E109) > 0 Then
       Sheets("alle").Range("B" & letztezeile + 1 & ":B" & letzteZeileNachImport).Value = "ERROR_CODE : 109"
    ElseIf InStr(Dateiname, E207) > 0 Then
        Sheets("alle").Range("B" & letztezeile + 1 & ":B" & letzteZeileNachImport).Value = "ERROR_CODE : 207"
    ElseIf InStr(Dateiname, E1302) > 0 Then
        Sheets("alle").Range("B" & letztezeile + 1 & ":B" & letzteZeileNachImport).Value = "ERROR_CODE : 1302"
    ElseIf InStr(Dateiname, resetting) > 0 Then
        Sheets("alle").Range("B" & letztezeile + 1 & ":B" & letzteZeileNachImport).Value = "resetting"
    ElseIf InStr(Dateiname, RDT) > 0 Then
        Sheets("alle").Range("B" & letztezeile + 1 & ":B" & letzteZeileNachImport).Value = "RDT & SCBE"
    ElseIf InStr(Dateiname, ETDR) > 0 Then
        Sheets("alle").Range("B" & letztezeile + 1 & ":B" & letzteZeileNachImport).Value = "*ETDR*"
    ElseIf InStr(Dateiname, TSENF) > 0 Then
        Sheets("alle").Range("B" & letztezeile + 1 & ":B" & letzteZeileNachImport).Value = "*TSENF*"
    ElseIf InStr(Dateiname, RAJP) > 0 Then
        Sheets("alle").Range("B" & letztezeile + 1 & ":B" & letzteZeileNachImport).Value = "RAJP"
    Else
        Sheets("alle").Range("B" & letztezeile + 1 & ":B" & letzteZeileNachImport).Value = "Error does not exist"
    End If
   
'Date is entered in the respective column?gt
Sheets("alle").Range("A" & letztezeile + 1 & ":A" & letzteZeileNachImport).Value = Date

'MsgBox Datei & vbNewLine & DateiName & vbNewLine & letztezeile & vbNewLine & letzteZeileNachImport, , "Meldung"
End Sub

(Code is in German as I'm still translating it, it was passed onto us some years ago)

Code above gives this code:
Run-time error '1004':
Method 'GetOpenFilename' of object '_Application' failed

I've tried changing the `Datei = Application.GetOpenFilename("csv Files (*.csv), *.csv")` to the function found here, but I can't get it to work, error below (I'm recently new to VBA, and it's the first time I've touched functions, let alone MacOS VBA)

Compile error:
Type mismatch


Any help is appreciated!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Have a look at Ron's macexcel.com site.
 
Upvote 0
And please remember:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Selecting multiple files in MacOS VBA and adding that data to main workbook
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
Hey Rory, thank you for the answer!

Didn't know that, thank you for clarifying. And I've seen his website multiple times but sadly it was no help at all with this specific problem.
 
Upvote 0
Upvote 0
Okay so I've checked the website and it was indeed a newer solution then the one that I found. The problem is, now I can't seem to copy the sheet data to my main WB. Code is below:

VBA Code: 
Sub Select_File_Or_Files_Mac()
    'Select files in Mac Excel with the format that you want
    'Working in Mac Excel 2011 and 2016 and higher
    'Ron de Bruin, 20 March 2016
    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
    Dim OneFile As Boolean
    Dim FileFormat As String
    Dim Proactive As Workbook
    
    'Here we set the proactive file to be named Proactive in the function
    Set Proactive = Workbooks("Proactive Cleaning_V2.0.1.xlsm")
    
    'In this example you can only select csv files
    'See https://www.macexcel.com/examples/filesandfolders/selectfiles/ to know how to use other and more formats.
    FileFormat = "{""public.comma-separated-values-text""}"

    ' Set to True if you only want to be able to select one file
    ' And to False to be able to select one or more files
    OneFile = False

    On Error Resume Next
    MyPath = MacScript("return (path to desktop folder) as String")
    'Or use A full path with as separator the :
    'MyPath = "HarddriveName:Users::Desktop:YourFolder:"

    'Building the applescript string, do not change this

        'This is only Mac Excel 2016 or higher
        If OneFile = True Then
            MyScript = _
                "set theFile to (choose file of type" & _
                " " & FileFormat & " " & _
                "with prompt ""Please select a file"" default location alias """ & _
                MyPath & """ without multiple selections allowed) as string" & vbNewLine & _
                "return posix path of theFile"
        Else
            MyScript = _
                "set theFiles to (choose file of type" & _
                " " & FileFormat & " " & _
                "with prompt ""Please select a file or files"" default location alias """ & _
                MyPath & """ with multiple selections allowed)" & vbNewLine & _
                "set thePOSIXFiles to {}" & vbNewLine & _
                "repeat with aFile in theFiles" & vbNewLine & _
                "set end of thePOSIXFiles to POSIX path of aFile" & vbNewLine & _
                "end repeat" & vbNewLine & _
                "set {TID, text item delimiters} to {text item delimiters, ASCII character 10}" & vbNewLine & _
                "set thePOSIXFiles to thePOSIXFiles as text" & vbNewLine & _
                "set text item delimiters to TID" & vbNewLine & _
                "return thePOSIXFiles"
        End If

    MyFiles = MacScript(MyScript)
    On Error GoTo 0

    'If you select one or more files MyFiles is not empty
    'We can do things with the file paths now like I show you below
    If MyFiles <> "" Then
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With

        MySplit = Split(MyFiles, Chr(10))
        For N = LBound(MySplit) To UBound(MySplit)

            'Get file name only and test 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
                ' testing code from here
                    If mybook.Name Like "*ERROR1302*" Then
                    Workbooks(Proactive).Worksheets("Error1302").Cells.Clear
                    On Error Resume Next
                    Workbooks(mybook).Worksheets(1).Cells.Copy Workbooks(Proactive).Worksheets("Error1302").Range("A1")
                    
                    ElseIf mybook.Name Like "*ERROR207*" Then
                    Workbooks(Proactive).Sheets("Error207").UsedRange.Clear
                    On Error Resume Next
                    Workbooks(mybook).Sheets(1).Cells.Copy Workbooks(Proactive).Worksheets("Error207").Range("A1")
                    
                    ElseIf mybook.Name Like "*ReinitiateActivationJobPending*" Then
                    MsgBox ("reinitiate")
                    ElseIf mybook.Name Like "*tripstatistics*" Then
                    MsgBox ("Trip")
                    ElseIf mybook.Name Like "*resetting*" Then
                    MsgBox ("reset")
                    Else
                    MsgBox ("File not set in macro, please add it where this message appears") 'if this message appears, simply add a "elseif" like the ones above with part of the name of the file
                    Exit Sub
                    End If
                    
                    
                    
                    
                    
                
                'to here
                
                    mybook.Close savechanges:=False
                End If
            Else
                MsgBox "We skip 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


From what I can see, the problem is when it runes this bit and it gives me a run time error 13: type mismatch
VBA Code: 
If mybook.Name Like "*ERROR1302*" Then
         Workbooks(Proactive).Worksheets("Error1302").Cells.Clear '<- Mismatch error happens here
         On Error Resume Next
         Workbooks(mybook).Worksheets(1).Cells.Copy Workbooks(Proactive).Worksheets("Error1302").Range("A1")

What I'm trying to achieve is: I open the files, then go through them one by one, copying the only sheet they contain onto a sheet on my main workbook, but making sure it replaces any content it had before.

Any thoughts?

RoryA said:
I suspect the version on his actual site is more up to date, and includes a sample that opens each of the workbooks to allow you to get data from them: Select files | Mac Excel Automation
Click to expand...

Also, thank you for pointing me in the right direction regarding the full sub!
 
Upvote 0
I figured it out!! I'll start by saying I'm new to VBA so this is probably something basic lol but the problem was that I was using this:
VBA Code: 
Workbooks(Proactive).Worksheets("Error1302").Cells.Clear

When I should be using this:
VBA Code: 
Proactive.Sheets("Error207").UsedRange.Clear

Full code if anyone ever meets a similar problem (it can probably be cut down a bit but it's working for me):


VBA Code: 
Sub Select_File_Or_Files_Mac()
    'Select files in Mac Excel with the format that you want
    'Working in Mac Excel 2016 and higher
    'Ron de Bruin, 20 March 2016 - edited by Nelson on 20 October 2021
    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
    Dim OneFile As Boolean
    Dim FileFormat As String
    Dim Proactive As Workbook
   
    'Here we set the proactive file to be named Proactive in the function
    Set Proactive = Workbooks("Proactive Cleaning_V2.0.1.xlsm")
   
    'In this example you can only select csv files
    'See https://www.macexcel.com/examples/filesandfolders/selectfiles/ to know how to use other and more formats.
    FileFormat = "{""public.comma-separated-values-text""}"

    ' Set to True if you only want to be able to select one file
    ' And to False to be able to select one or more files
    OneFile = False

    On Error Resume Next
    MyPath = MacScript("return (path to desktop folder) as String")
    'Or use A full path with as separator the :
    'MyPath = "HarddriveName:Users::Desktop:YourFolder:"

    'Building the applescript string, do not change this

        'This is only Mac Excel 2016 or higher
        If OneFile = True Then
            MyScript = _
                "set theFile to (choose file of type" & _
                " " & FileFormat & " " & _
                "with prompt ""Please select a file"" default location alias """ & _
                MyPath & """ without multiple selections allowed) as string" & vbNewLine & _
                "return posix path of theFile"
        Else
            MyScript = _
                "set theFiles to (choose file of type" & _
                " " & FileFormat & " " & _
                "with prompt ""Please select a file or files"" default location alias """ & _
                MyPath & """ with multiple selections allowed)" & vbNewLine & _
                "set thePOSIXFiles to {}" & vbNewLine & _
                "repeat with aFile in theFiles" & vbNewLine & _
                "set end of thePOSIXFiles to POSIX path of aFile" & vbNewLine & _
                "end repeat" & vbNewLine & _
                "set {TID, text item delimiters} to {text item delimiters, ASCII character 10}" & vbNewLine & _
                "set thePOSIXFiles to thePOSIXFiles as text" & vbNewLine & _
                "set text item delimiters to TID" & vbNewLine & _
                "return thePOSIXFiles"
        End If

    MyFiles = MacScript(MyScript)
    On Error GoTo 0

    'If you select one or more files MyFiles is not empty
    'We can do things with the file paths now like I show you below
    If MyFiles <> "" Then
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With

        MySplit = Split(MyFiles, Chr(10))
        For N = LBound(MySplit) To UBound(MySplit)

            'Get file name only and test 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
               
                    If mybook.Name Like "*ERROR1302*" Then
                    Proactive.Sheets("ERROR1302").UsedRange.Clear
                    mybook.Sheets(1).Cells.Copy Proactive.Worksheets("ERROR1302").Range("A1")

                   
                    ElseIf mybook.Name Like "*ERROR207*" Then
                    Proactive.Sheets("ERROR207").UsedRange.Clear
                    mybook.Sheets(1).Cells.Copy Proactive.Worksheets("ERROR207").Range("A1")

                    ElseIf mybook.Name Like "*ReinitiateActivationJobPending*" Then
                    Proactive.Sheets("ReinitiateActivationJobPending").UsedRange.Clear
                    mybook.Sheets(1).Cells.Copy Proactive.Worksheets("ReinitiateActivationJobPending").Range("A1")
                   
                    ElseIf mybook.Name Like "*tripstatistics*" Then
                    Proactive.Sheets("tripstatistics").UsedRange.Clear
                    mybook.Sheets(1).Cells.Copy Proactive.Worksheets("tripstatistics").Range("A1")
                   
                    ElseIf mybook.Name Like "*resetting*" Then
                    Proactive.Sheets("resetting").UsedRange.Clear
                    mybook.Sheets(1).Cells.Copy Proactive.Worksheets("resetting").Range("A1")
                   
                    Else
                    MsgBox ("File not set in macro, please add it where this message appears in the code and reselect the file in question") 'if this message appears, simply add a "elseif" like the ones above with part of the name of the file and sheet name
                   
                    Exit Sub
                    End If
                   
               
                    mybook.Close savechanges:=False
                End If
            Else
                MsgBox "We skip 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

It's still not complete but the main problem is fixed so I'll set this as the answer. I now need the data to go into a table on that sheet so I can have an history of the data used instead of replacing the data on the sheet

Again, thank you Rory for pointing me in the right direction!
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

R
  • Question
VBA Script: Saving worksheet as CSV on MacOS
Replies
1
Views
915
temphelp
T
H
  • Question
Macro to download zip, unpack and copy data
Replies
0
Views
78
HeinrichPaul
H
K
  • Solved
Combining 3 Worksheet_Change events
Replies
4
Views
296
Kevo
K
R
  • Question
How to combine the functions of having the ability to select multiple items from a drop down list and hide columns based on values of said list?
Replies
0
Views
216
rgree05
R
S
  • Question
VBA: All Sheets data to Master Sheet (excluding some sheet)
2
Replies
11
Views
390
kevin9999
K

Forum statistics

Threads
1,214,914
Messages
6,122,211
Members
449,074
Latest member
cancansova

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top