SOLVED

Open File Dialog Box Issue

%3CLINGO-SUB%20id%3D%22lingo-sub-2152080%22%20slang%3D%22en-US%22%3EOpen%20File%20Dialog%20Box%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2152080%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3A%3C%2FP%3E%3CP%3EI%20have%20used%20the%20Open%20File%20dialog%20Box%20VBA%20for%20years.%26nbsp%3B%20When%20I%20tried%20it%20on%20a%20workbook%20that%20is%20linked%20to%20Google%20Sheets%20(a%20connection)%2C%20it%20dims%20the%20open%20button%20when%20I%20select%20the%20workbook.%26nbsp%3B%20Any%20ideas%20why%20it%20is%20dimmed%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20the%20code%20to%20call%20it%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECall%20FileDialogueOpen%3C%2FP%3E%3CP%3EIf%20strCancel%20%3D%20%22Y%22%20Then%3CBR%20%2F%3EMsgBox%20(%22An%20Open%20Error%20Occurred%20Importing%20Your%20File%20Selection%22)%3CBR%20%2F%3EExit%20Sub%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20the%20actual%20code%3A%3C%2FP%3E%3CP%3EPrivate%20Sub%20FileDialogueOpen()%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20False%3CBR%20%2F%3EDim%20dlgOpenFile%20As%20FileDialog%3CBR%20%2F%3EDim%20strFilePathToQualityException%20As%20String%3C%2FP%3E%3CP%3EstrFilePathToQualityException%20%3D%20%22%22%3CBR%20%2F%3ESet%20dlgOpenFile%20%3D%20Application.FileDialog(msoFileDialogOpen)%3CBR%20%2F%3EWith%20dlgOpenFile%3CBR%20%2F%3E.AllowMultiSelect%20%3D%20False%3CBR%20%2F%3E.Filters.Clear%3CBR%20%2F%3E.Filters.Add%20%22Excel%202007-2016%20Workbook%22%2C%20%22*.xlsx%22%3CBR%20%2F%3E.FilterIndex%20%3D%201%3CBR%20%2F%3E.Title%20%3D%20%22Select%20Quality%20Exception%20Excel%20Report%22%3CBR%20%2F%3E.Show%3CBR%20%2F%3EIf%20.SelectedItems.Count%20%26lt%3B%201%20Then%3CBR%20%2F%3EstrCancel%20%3D%20%22Y%22%3CBR%20%2F%3EExit%20Sub%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EstrFilePathToQualityException%20%3D%20.SelectedItems(1)%3CBR%20%2F%3EEnd%20With%3C%2FP%3E%3CP%3EWorkbooks.Open%20strFilePathToQualityException%3CBR%20%2F%3EstrFileNameOpenedByUser%20%3D%20ActiveWorkbook.Name%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20a%20solid%20piece%20of%20code%2C%20but%20it%20doesn't%20work%20when%20I%20try%20to%20open%20a%20Google%20Connected%20workbook%20because%20the%20Open%20button%20is%20dimmed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20advice%20would%20be%20helpful.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3ERich%20Locus%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2152080%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2152322%22%20slang%3D%22en-US%22%3ERe%3A%20Open%20File%20Dialog%20Box%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2152322%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F319870%22%20target%3D%22_blank%22%3E%40Rich_Locus%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20is%20hard%20to%20test%20if%20you%20don't%20have%20a%20workbook%20with%20a%20link%20to%20Google%20Sheets...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2221367%22%20slang%3D%22en-US%22%3ERe%3A%20Open%20File%20Dialog%20Box%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2221367%22%20slang%3D%22en-US%22%3EThanks%20Hans.%3CBR%20%2F%3ERich%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello:

I have used the Open File dialog Box VBA for years.  When I tried it on a workbook that is linked to Google Sheets (a connection), it dims the open button when I select the workbook.  Any ideas why it is dimmed?

 

Here's the code to call it:

 

Call FileDialogueOpen

If strCancel = "Y" Then
MsgBox ("An Open Error Occurred Importing Your File Selection")
Exit Sub
End If

 

Here's the actual code:

Private Sub FileDialogueOpen()
Application.ScreenUpdating = False
Dim dlgOpenFile As FileDialog
Dim strFilePathToQualityException As String

strFilePathToQualityException = ""
Set dlgOpenFile = Application.FileDialog(msoFileDialogOpen)
With dlgOpenFile
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel 2007-2016 Workbook", "*.xlsx"
.FilterIndex = 1
.Title = "Select Quality Exception Excel Report"
.Show
If .SelectedItems.Count < 1 Then
strCancel = "Y"
Exit Sub
End If
strFilePathToQualityException = .SelectedItems(1)
End With

Workbooks.Open strFilePathToQualityException
strFileNameOpenedByUser = ActiveWorkbook.Name

End Sub

 

It is a solid piece of code, but it doesn't work when I try to open a Google Connected workbook because the Open button is dimmed.

 

Any advice would be helpful.

 

Thanks,

Rich Locus

 

2 Replies
best response confirmed by Rich_Locus (Occasional Contributor)
Solution

@Rich_Locus 

This is hard to test if you don't have a workbook with a link to Google Sheets...

Thanks Hans.
Rich