Adding data from different workbook into table on main workbook and add a new collumn with part of file name

Nelsini

New Member
Joined
May 13, 2021
Messages
25
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
Hello!

I've been struggling with this code:

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

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

                    ElseIf mybook.Name Like "*ERROR1302*" Then
                    Proactive.Sheets("ERROR1302").UsedRange.Clear
                    mybook.Sheets(1).Cells.Copy Proactive.Worksheets("ERROR1302").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
                    mybook.Close savechanges:=False
                    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

What I have now is: I open several .csv files, then I run if statements depending on the name of the file in order to insert it into a specific sheet on my main workbook. The code clears that sheet's content and replaces it with the data from the opened files.

What I actually want to achieve: I open several .csv files, then I run if statements depending on the name of the file in order to insert it into a specific sheet on my main workbook. I want to add that data into tables in specific sheets (one table per file/error type, one table per sheet), since I want to have a history of the data used, and add the date on the file name to the third column of said table.

File name example: ERROR207_2021-10-20.csv
Content example:
Car_VIN,count
VIN123143214324,1
VIN132143265675,20
VIN210193543939,13
...

The table on the main workbook would turn out to be something like this:

Car_VINCountDate
VIN12314321432412021-10-20
VIN132143265675202021-10-20
VIN210193543939132021-10-20

I'm really new to VBA, and we have a working solution with Power queries on W10 but our company is trading windows for MacOS, I need to "translate" that into VBA.

Any help is appreciated and if any additional info is needed please let me know!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Nelsini

New Member
Joined
May 13, 2021
Messages
25
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
Crossposted on StackOverflow here (can't edit)

If anyone has any thoughts please let me know, I can't figure it out :(
 

Nelsini

New Member
Joined
May 13, 2021
Messages
25
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
Update
So I've tried copying the used range of the ERROR207_2021-10-20.csv workbook like this:

VBA Code:
Set mybook = Nothing
On Error Resume Next
Set mybook = Workbooks.Open(MySplit(N))
On Error GoTo 0
If Not mybook Is Nothing Then
Dim mybookcells As Range
Set mybookcells = mybook.Sheets(1).UsedRange
If mybook.Name Like "*ERROR207*" Then
mybookcells.Copy Proactive.Sheets("ERROR207").Cells(Row.Count, 1).End(xlUp).Offset(1,0)

This gives me a Run-time error '424': Object required
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,489
Messages
5,770,388
Members
425,615
Latest member
tomenzo123

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
Top