Macro to open file into an existing worksheet and sort by column (MAC 2011)

The macro I have so far allows me to click a button and it's prompted to open a .csv or .xls file. However, I need assistance on when I click on the file I want to import to have it imported into the tab labeled "Current" and sort data from the imported file accordingly by column. I've attached the reporting tool and the file I want to upload to test.

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")
    MyScript = _
    "set applescript's text item delimiters to "","" " & vbNewLine & _
               "set theFiles to (choose file of type " & _
             " {""com.microsoft.excel.xls"",""public.comma-separated-values-text"",""org.openxmlformats.spreadsheetml.sheet""}" & _
               "with prompt ""Please select a file or files"" default location alias """ & _
               MyPath & """ multiple selections allowed true) as string" & vbNewLine & _
               "set applescript's text item delimiters to """" " & vbNewLine & _
               "return theFiles"

    MyFiles = MacScript(MyScript)
    On Error GoTo 0
   
End Sub
DSE-Carelog-Report.xls
US-Bank-PHD-17-Mar-2015-13-25.xls
AckeemKAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SimonCommented:
I think you'll need to confirm how to map the columns. There are some matches, some likely guesses and some that I can't see any obvious match for.
Carelog-ImportFieldMapping.pngPlease confirm whether the fields I've identified as matching names and similar names are correct and that the rest should be omitted before I proceed.
0
AckeemKAuthor Commented:
So the imported file column titles can vary depending on the platform the file is pulled from. After speaking to someone in operations, they mentioned that there are some key columns that all three platforms have although they might have different names. Below I have the column headers that the master report should have based on the information I received. Each column header has two names based on the platform it may be pulled from. So depending on the file, it can be either one. But to start out basic, I will just have these as my concrete column headers for the master report since I want to start simple.

ID/SR Number
Subject/Problem Summary
Organization/Owner Group
Severity
Status
Product
Latest Update/Last Update
Owner/Assignee

The last two columns (SR Status Update & SR Resolution Summary) would be there for the engineer to input notes and save it down to be used to review at their own time or when they imported a updated file.
0
SimonCommented:
OK, here a version that works for the sample import file you supplied. It works on ordinal column positions rather than names. If the other platforms produce different column orders, you'd need to determine which column order to work with (by testing for something that is unique to that platform's version).
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
    Dim filePath As Variant
    Dim dataRange As Range 'Range in the import sheet to get data from (excludes header row)
    Dim destRange As Range 'Range in the destination sheet to write to (end of usedrange.rows +1)
    
    'On Error Resume Next
    MyPath = MacScript("return (path to documents folder) as String")
    MyScript = _
    "set applescript's text item delimiters to "","" " & vbNewLine & _
               "set theFiles to (choose file of type " & _
             " {""com.microsoft.excel.xls"",""public.comma-separated-values-text"",""org.openxmlformats.spreadsheetml.sheet""}" & _
               "with prompt ""Please select a file or files"" default location alias """ & _
               MyPath & """ multiple selections allowed true) as string" & vbNewLine & _
               "set applescript's text item delimiters to """" " & vbNewLine & _
               "return theFiles"

    MyFiles = MacScript(MyScript)
    On Error GoTo 0
   
   If MyFiles <> "" Then
        MySplit = Split(MyFiles, ",")
        For Each filePath In MySplit
            Set mybook = Application.Workbooks.Open(filePath, , True, , , , , , , , , , False) 'open read-only. Don't add to MRU
            Set dataRange = mybook.Sheets(1).UsedRange.Offset(1)
            Set dataRange = dataRange.Resize(dataRange.Rows.Count - 1)
            Debug.Print dataRange.Address
            Set destRange = ThisWorkbook.Sheets("Current").UsedRange
            Set destRange = destRange.Offset(destRange.Rows.Count).Resize(dataRange.Rows.Count)
            Debug.Print destRange.Address
            destRange.Columns(1).Value = dataRange.Columns(2).Value 'SR
            destRange.Columns(2).Value = dataRange.Columns(10).Value 'Subject
            destRange.Columns(3).Value = dataRange.Columns(6).Value 'Severity
            destRange.Columns(4).Value = dataRange.Columns(12).Value 'owner
            destRange.Columns(5).Value = dataRange.Columns(11).Value 'Status
            destRange.Columns(7).Value = dataRange.Columns(8).Value 'Product
            destRange.Columns(8).Value = dataRange.Columns(13).Value 'Owning group
            destRange.Columns(9).Value = dataRange.Columns(9).Value 'Version
            destRange.Columns(10).Value = dataRange.Columns(14).Value 'Creation Date
            destRange.Columns(11).Value = dataRange.Columns(16).Value 'LastUpdated
            mybook.Close
        Next

   End If
End Sub

Open in new window


The mapping to the desired column order is lines 37-46 of the above code listing. You should test and tweak as necessary.

I note that, for example the date format in the import files doesn't match the ISO datetime in the CURRENT worksheet.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

AckeemKAuthor Commented:
Thank you for this, it is greatly appreciated. Do you know if it would be possible to include a IF statement for the desired column order or would that not work properly? For instance, if the column header (A1) reads "SR Number" or "ID", set the destination range to A1 in the current tab? If not, I know it would require some more testing depending on the platform.
0
SimonCommented:
>Do you know if it would be possible to include a IF statement for the desired column order or would that not work properly? For instance, if the column header (A1) reads "SR Number" or "ID", set the destination range to A1 in the current tab?
Yes, easy enough to insert a test for this. I had assumed that you wanted to append to the data in the current tab.

This version includes testing the value of cell A1 in the import sheet and clearing the current sheet if it equals "SR Number
" or "ID". I note that the sample import you provided had "SR#" in cell B1, while cell A1 was empty.

Sub Select_Folder_On_Mac()
    Dim folderPath As String
    Dim RootFolder As String

    On Error Resume Next
    RootFolder = MacScript("return (path to desktop folder) as String")
    folderPath = MacScript("(choose folder with prompt ""Select the folder""" & _
    "default location alias """ & RootFolder & """) as string")
    On Error GoTo 0

    If folderPath <> "" Then
        MsgBox folderPath
    End If
End Sub



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
    Dim filePath As Variant
    Dim dataRange As Range 'Range in the import sheet to get data from (excludes header row)
    Dim destRange As Range 'Range in the destination sheet to write to (end of usedrange.rows +1)
    
    'On Error Resume Next
    MyPath = MacScript("return (path to documents folder) as String")
    MyScript = _
    "set applescript's text item delimiters to "","" " & vbNewLine & _
               "set theFiles to (choose file of type " & _
             " {""com.microsoft.excel.xls"",""public.comma-separated-values-text"",""org.openxmlformats.spreadsheetml.sheet""}" & _
               "with prompt ""Please select a file or files"" default location alias """ & _
               MyPath & """ multiple selections allowed true) as string" & vbNewLine & _
               "set applescript's text item delimiters to """" " & vbNewLine & _
               "return theFiles"

    MyFiles = MacScript(MyScript)
    On Error GoTo 0
   
   If MyFiles <> "" Then
        MySplit = Split(MyFiles, ",")
        For Each filePath In MySplit
            Set mybook = Application.Workbooks.Open(filePath, , True, , , , , , , , , , False) 'open read-only. Don't add to MRU
            testVal = mybook.Sheets(1).Cells(1, 1).Value 'Check the value of cell A1 to determine the version of the import sheet
            Set dataRange = mybook.Sheets(1).UsedRange.Offset(1)
            Set dataRange = dataRange.Resize(dataRange.Rows.Count - 1)
            Debug.Print dataRange.Address
            Set destRange = ThisWorkbook.Sheets("Current").UsedRange
            'Clear content of Current sheet apart from headers
            If testVal = "SR Number" Or testVal = "ID" Then
                destRange.Offset(1).Clear
                Set destRange = destRange.Resize(1, 1)
            End If
            Set destRange = destRange.Offset(destRange.Rows.Count).Resize(dataRange.Rows.Count)
            Debug.Print destRange.Address
            destRange.Columns(1).Value = dataRange.Columns(2).Value 'SR
            destRange.Columns(2).Value = dataRange.Columns(10).Value 'Subject
            destRange.Columns(3).Value = dataRange.Columns(6).Value 'Severity
            destRange.Columns(4).Value = dataRange.Columns(12).Value 'owner
            destRange.Columns(5).Value = dataRange.Columns(11).Value 'Status
            destRange.Columns(7).Value = dataRange.Columns(8).Value 'Product
            destRange.Columns(8).Value = dataRange.Columns(13).Value 'Owning group
            destRange.Columns(9).Value = dataRange.Columns(9).Value 'Version
            destRange.Columns(10).Value = dataRange.Columns(14).Value 'Creation Date
            destRange.Columns(11).Value = dataRange.Columns(16).Value 'LastUpdated
            mybook.Close
        Next
   End If
End Sub

Open in new window

0
AckeemKAuthor Commented:
This works perfectly for what I had in mind. If I wanted to have this test be done for each column header of the file that's being imported that may have a different name, would I just write something along these lines?

If testVal = "Subject" Or testVal = "Problem Summary" Then
                destRange.Offset(2).Clear
                Set destRange = destRange.Resize(2, 2)
            End If
If testVal = "Owner" Or testVal = "Assignee" Then
                destRange.Offset(4).Clear
                Set destRange = destRange.Resize(4, 4)
            End If

And so on for the column headers that may appear different depending on the platform the file being imported is downloaded from.

I also was trying to figure out a macro to sort the rows based on the latest updated (newest to oldest) and remove the duplicates once the file is imported into the current tab. Once the duplicates are removed (signifying nothing has changed about a particular SR number), there may be some rows with the same SR number as shown in the attachment but one may be more recent than the other with different notes by the user. Is there a way to highlight these rows so that the user can see that things have changed for this particular SR number based on their last update?
0
SimonCommented:
>This works perfectly for what I had in mind. If I wanted to have this test be done for each column header of the file that's being imported that may have a different name, would I just write something along these lines?

What I suggested was based on column positions, rather than checking each column title. While you could make the import process infinitely sophisticated, it is normally desirable to accept just one or a few import file structures and validate any potential import file against those criteria. Reject the file if it doesn't match the pre-defined criteria.

Please reppost your latest requirements in a new question. All possible, but it's a separate subject from the original question on importing and matching columns.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Mac OS X

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.