AckeemK
asked on
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
"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
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
"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
Simon
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.
Please 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.
Please 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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
>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.
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 SubASKER
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?
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?
>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.
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.