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.
A big problem we found in 2023 was that ChatGPT persisted 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 showed code (Application.GetOpenFilename) that only works in Excel, but ChatGPT shows it for Word and PowerPoint too.
2026 update: AI services including ChatGPT have improved a LOT since this article was written. Modern AI should not make these mistakes.
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
