Tag: Excel

VBA Mac File Dialog Selecting Multiple Files

Posted on by Paul Shultz in Excel, vba, Word

Previously I have written about selecting a file from a Microsoft VBA using a file dialog window on a Mac. This approach used an Applescript and selected a single file. This approach was limited to selecting a single file located on the local hard drive. Recently I had the need to updated this approach to allow selecting multiple files that are located on any drive/cloud.

The ability to select multiple files in the dialog window required a small change to the Applescript. The text “multiple selections allowed false” changed “false” to “true.” This allows selecting multiple files that the Applescript returns.

mypath = MacScript("return (path to documents folder) as String")
    
sMacScript = "set applescript's text item delimiters to "","" " & vbNewLine & _
"try " & vbNewLine & _
"set theFiles to (choose file " & _
"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 & _
"on error errStr number errorNumber" & vbNewLine & _
"return errorNumber " & vbNewLine & _
"end try " & vbNewLine & _
"return theFiles"

inFiles = MacScript(sMacScript)     ' get filenames

When the MacScript is run it returns a string of full path filenames separated by “,” unless no files are selected then “-128” is returned. The string of filenames are easily split into a array file filenames for VBA use. For the VBA to access each file two changes are required to each filename. First the Mac directory separator “:” is replaced with “/” symbol. The second change is to add a prefix “/Volumes/” to a full path filename. This allows the use of different drives/cloud files. In my previous post I removed “Macintosh HD” for the path name.

filenameSplit = Split(inFiles, ",")
For N = LBound(filenameSplit) To UBound(filenameSplit)
    selectedFile = "/Volumes/" & Replace(filenameSplit(N), ":", "/")

    <... DO WORK ...>

Next

This updated Applescript and code was used in a Word VBA but should work well with Excel on the Mac.

Excel VBA File Dialog on a Mac

Posted on by Paul Shultz in Excel

I’ve been creating Microsoft VBAs for years to solve different problems ranging from Finance to Engineering. These VBAs have been developed and hosted on Windows platforms using different versions of Microsoft Office products. Recently I had a VBA requirement where the client used Excel (365) on a Mac. For tis application the key differences between the two products/OSs were:

  • No userform development UI on Mac
  • Different file dialog
  • Full path filename parsing

To solve the the userform development UI issue, I just developed the userform on Windows. When the VBA executed on the Mac the userform was properly displayed without any issues. Any changes to the userform had to be done on a Windows platform.

The second problem was more difficult and required some research. Prevously I’ve accessed files using the Application.FileDialog function with the msoFileDialogFilePicker parameters. It’s straight forward and I have used this code many times. A code segment is shown below. 

'Display a Dialog Box that allows to select a single file.
'The path for the file picked will be stored in fullpath variable
With Application.FileDialog(msoFileDialogFilePicker)
    'Makes sure the user can select only one file
    .AllowMultiSelect = False
    'Filter to just the following types of files to narrow down selection options
    .Filters.Add "CSV/CRC Files", "*.csv; *.crc", 1
    'Show the dialog box
    .Show
        
    'Store in fullpath variable if file selected
    If .SelectedItems.Count <> 0 Then
        fullpath = .SelectedItems.Item(1)
    Else
        MsgBox ("No file selected. Exiting program...")
        Exit Sub
    End If
End With

The problem is Application.FileDialog is not supported by Excel on the MacOS. I am thankful someone else had already solved this problem using an Applescript. I was able to reuse the posted script without any modifications. I created a function for each operating system to create a file dialog window and return the user selected file. On a Mac, if the user doesn’t select a file, the string “-128” (error code userCanceledErr) is returned. So the Windows function also returns “-128” when the user doesn’t select a file.

Function BrowseMac(mypath As String) As String
  Dim sMacScript As String
  
  sMacScript = "set applescript's text item delimiters to "","" " & vbNewLine & _
    "try " & vbNewLine & _
    "set theFiles to (choose file " & _
    "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 & _
    "on error errStr number errorNumber" & vbNewLine & _
    "return errorNumber " & vbNewLine & _
    "end try " & vbNewLine & _
    "return theFiles"
  BrowseMac = MacScript(sMacScript)
End Function

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Function BrowseWin() As String
    'Display a Dialog Box that allows to select a single file.
    'The path for the file picked will be stored in fullpath variable
    With Application.FileDialog(msoFileDialogFilePicker)
        'Makes sure the user can select only one file
        .AllowMultiSelect = False
        'Filter to just the following types of files to narrow down selection options
        .Filters.Add "Text Stock Files", "*.txt, 1"
        .Title = "Select Input Stock File"
        'Show the dialog box
        .Show
        
        'Store in fullpath variable if file selected
        If .SelectedItems.Count <> 0 Then
            BrowseWin = .SelectedItems.Item(1)
        Else
            BrowseWin = "-128"  ' what Mac OS returns when file is not selected
        End If
    End With
End Function

Since I was developing on Windows 10 and testing using both operating systems, I used Application.OperatingSystem to determine the host OS to call the appropriate routine. On MacOS Application.OperatingSystem includes “Macintosh” in the returned string. The call to the Mac file browser also uses an Applescript to the provide the documents folder directory path.

' determine OS and get input file via dialog box
theOS = Application.OperatingSystem
If (InStr(1, theOS, "Macintosh", vbTextCompare) = 0) Then   ' not Mac
    ' Windows Version
    fullFilename = BrowseWin
    fileName = getWINFName(fullFilename)
Else
    ' Mac Version
    fullFilename = BrowseMac(MacScript("return (path to documents folder) as String"))
    fileName = getMACFName(fullFilename)
End If
    
' check for a good file name
If fullFilename = "-128" Then
    MsgBox ("File not selected, no action taken.")
    Exit Sub
End If

The returned filename has the full path name. For this application I wanted to capture the filename only. So another difference between the operating sytsem is Windows uses the “\” character and MacOS used the “:” character to separate the path. 

Function getWINFName(pf) As String: getWINFName = Mid(pf, InStrRev(pf, "\") + 1): End Function
Function getMACFName(pf) As String: getMACFName = Mid(pf, InStrRev(pf, ":") + 1): End Function

Working with the texted based input file was the same for both Windows 10 and MacOS 10. Below is a small code snippet showing the opening the file, reading text line, and closing the file once finished.

Open fullFilename For Input As #1
Do Until EOF(1)
    ' get input record and separate items
    ' record data time, open, high, low, close, [volume]
    Line Input #1, textLine             ' input record
    inputSplit = Split(textLine, ",")   ' split on '

    < good code stuff >

Loop
Close #1

The Open with the full path name worked on the Mac, but I found if you are using Workbooks.Open with Excel 2016, then the full path filename needs to be modified by changing “:” to “/” and removing the “Macintosh HD” from the full path. I found this nice code on stack overflow, which worked great. [NOTE: working on a new project I found that adding “/Volumes/” to a full path filename allows the user to retain volume information, which is useful for other drives/cloud based files. See my article on accessing multiple files using a VBA using Mac OS.]

If CInt(Split(Application.Version, ".")(0)) >= 15 Then 'excel 2016 support
    wbName = Replace(wbName, ":", "/")
    wbName = Replace(wbName, "Macintosh HD", "", Count:=1)
End If

So my first experience developing an Excel VBA for MacOS had small, but solvable technical hurdles, which were solved by using a good search engine and leveraging other developer’s code.

Excel Selenium Element Test

Posted on by Paul Shultz in Excel, Web Scraping

During web scraping sometimes an expected element is not present. This recently happened during a web scraping project when the client input data was not correct causing the page to load with an error and didn’t have the expected data to scrape. The web scraping was being done in Excel using Selenium and Chrome browser. The error handling can be done a couple of different ways. I used the approach of assigning a webelement object to the expected element and then test if the object is Nothing.

Element Test Code

' class that has distance in miles
Set miClass = driver.FindElementByClass("mi", timeout:=0, Raise:=False)
If Not miClass Is Nothing Then
    {Element Present}
Else
    { Element not Present, Error Handling}

This project required loading almost 20,000 web pages. Sometimes the web page url failed to load. Although not directly tied to testing for the present of an element this error case still needed to be handled. For this case Excel VBA error handing is used. Multiple attempts are made to load the url. If the web page loads then the “attempt loop” is exited early. If the maximum number of attempts is reached, then error handling is used.

In an Excel VBA, On Error is used to control the error handing. Before making the web page loading attempt On Error GoTo and Exit For are used to manage the multiple attempts.

Web Page Loading Error handling

' attempt to get website
On Error GoTo tryAgain    ' if error GoTo tryAgain
For try = 1 To Attempts   ' number of attempts to try (const)
    ' get website
    driver.Get url        ' attempt to load web page, if error GoTo tryAgain
    Exit For              ' web page loaded exit For
tryAgain:
Next try                  ' next attempt
On Error GoTo 0           ' reset Error handling to stop on error
                    
If (try <> (Attempts + 1)) Then   ' Test if maximum attempts were reached
    {Page Loaded, Do Stuff}
Else
    {Page Didn't Load, Error Handling Stuff}

Excel Selenium Web Scraping Error 33

Posted on by Paul Shultz in Excel, Web Scraping

I’ve done Chrome web scraping in both Python and Excel using Selenium on a Windows 10 platform. I have also web scraped in Python using BeatifulSoup4. A new client had a requirement to find the distance between two zip codes. The client had created an Excel worksheet with a matrix of zip codes (vertical) and cities (horizontal). I found a web site that calculates the distance between two zip codes in a web page. The zip codes are part of the web page URL I converted the cities to city airport zip codes so I just needed to read rows and columns to create the URL and the scrape the results. Easy right?

Error 33

I implemented the VBA code and got a runtime error 33 when opening the Chrome browser. I checked other Excel scrapers that I had written and they all failed. So something was wrong with my Excel environment. An Internet search indicated that the version of Chrome and the chromedriver were not the same. I remembered that I had created a directory for Selenium webdrivers and added it to the system path. I updated the driver to match the Chrome version and I still got the same runtime error. I then tried one of my Python scrapers and they worked! So my problem was with Excel. I continued my search and couldn’t find a resolution until….

Excel Selenium Environment

To setup the Excel environment you need to add SeleniumBasic library, which I already had and the version hadn’t changed since 2016. To add the library to your VBA you need to select the Selenium Type Libary (editor Tools > References…). As I re-read the installation instructions I found that the driver needs to be placed in the same directory as the SeleniumBasic library, which for me was not the webdriver directory I had already updated. That was the difference. Excel did not use the system path for the webdriver.

Once I updated the chromdriver in the SeleniumBasic directory very thing started working again. Watch out for Chrome updates. For my environment I need to update the driver in two locations – the system path directory (Python) and the SeleniumBasic directory (Excel). A good description on setting up Excel to work with Selenium can be found at myOnlineTrainingHub.