Get ChatGPT or Copilot to show working Office VBA code to select and open a Word, Excel or PowerPoint document. It’s an example of how to work with ChatGPT to get the code you need and its limitations.
In episode 1 of this saga, Office Watch showed how to use ChatGPT to make sample Office VBA code. It’s not perfect, but a way to get examples you can work with.
A big problem we found was that ChatGPT seems to persist with lines of code that don’t work, suggesting the same code over and over.
For example, for selecting an Office file to open, ChatGPT shows code (Application.GetOpenFilename) that only works in Excel, but ChatGPT shows it for Word and PowerPoint too.
For this article, we found a way to get ChatGPT to show working code to select a file in Office VBA. Even then, you’ll see ChatGPT keeps proposing another line of code that doesn’t work (Filtering the view for a File Open dialog). We’ll explain the problem and the correct code.
In short: there’s a lot of talk about ChatGPT replacing programmers … maybe that day will come but not anytime soon.
Selecting a document for Word, PowerPoint and Excel
Here’s how we got ChatGPT to show Office VBA that works to select and open a document for any of the ‘big three’ Office apps.
Aside from a good code example (at the bottom of this article), it’s an example of how to use ChatGPT to give better code using gradually more refined requests.
Asking “Word VBA to choose document then open it”, “PowerPoint VBA to choose document then open it,” “Microsoft Office VBA to choose document then open it” and other variations all suggested the same incorrect code line.
The trick we found was to explicitly tell ChatGPT which Office VBA method to use. With that huge, massive hint, the AI was able to show working code.
How to use Application.FileDialog in Office VBA
We know the ‘secret sauce’ to selecting a file in Office VBA, it’s called Application.FileDialog. Using that big hint, we could get ChatGPT to show working code like this; “How to use Application.FileDialog in Office VBA”
That code only works in Word. As we’ve already noted, the code Documents.Open only works for Word ḃut the current ChatGPT uses the same line for Excel and PowerPoint too.
How to use Application.FileDialog in Office VBA showing only .ppt files
When you try it, the File Open dialog box shows all files, not just Office documents. So we went back to ChatGPT with a more specific request “How to use Application.FileDialog in Office VBA showing only .ppt files”
As you can see, there’s now an additional line which adds a Filter to the File Dialog object. Unfortunately, it doesn’t work, raising an error “Method of data member not found”.
The correct syntax is to add each filter separately like this:
objFileDialog.Filters.Add "PowerPoint Files (*.pptx)", "*.pptx"
objFileDialog.Filters.Add "All Files", "*.*"
Obviously, you can change that to show Word or Excel files instead.
Using WITH to add settings
What ChatGPT doesn’t show is a better way to setup any object, like Application.FileDialog, using With … End With. That lets you do many things on an object in a clearer way, like this:
Dim objFileDialog As FileDialog
Set objFileDialog = Application.FileDialog(msoFileDialogOpen)
‘ settings for the File Open dialog
With objFileDialog
.Filters.Add "Word Files (*.docx)” , “*.docx”
.Filters.Add "All Files (*.*)” , “*.*”
.InitialFileName = "C:\"
.AllowMultiSelect = False
.Title = “Choose a document so I can get some work done!”
End With
The Complete Code
Here’s the complete code, based on the ChatGPT original plus fixes.
Sub OpenWordFile()
' Declare variables
Dim objFileDialog As FileDialog
Dim strFile As String
' Create a FileDialog object
Set objFileDialog = Application.FileDialog(msoFileDialogOpen)
‘ setting for the File Open dialog
' Replaces this ChatGPT line which does not work
‘objFileDialog.Filter = "Word Files (*.docx;*.doc)|*.docx;*.doc|All Files (*.*)|*.*"
With objFileDialog
.Filters.Add "Word Files (*.docx)” , “*.docx”
.Filters.Add "Old Word Files (*.doc)” , “*.doc”
.Filters.Add "All Files (*.*)” , “*.*”
End With
' Show the file picker dialog
If objFileDialog.Show = -1 Then
' Get the selected file
strFile = objFileDialog.SelectedItems(1)
' Open the selected file
Documents.Open FileName:=strFile
Else
' No file was selected
MsgBox "No file was selected. Please select a file to open.", vbExclamation
End If
End Sub
Easily learn and make Office VBA with ChatGPT
Inside the Office VBA/MOTW changes
Excel merge arrays into a single array with VBA
