VBA - Open .csv file from open folder

[fdanford]

Hey guys,

This is my first time posting in the forums, so I hope I'm in the right place. I'm writing this program using VBA in Excel 2007 and am somewhat experienced in VBA. I'm trying to give the user of the program an option to browse a folder, select the excel file that they want to open, and then double click it to open it. To accomplish this, I am simply using the shell function to open the folder containing the files. However, when I double click the excel file it doesn't open or do anything. I initially thought that it might be due to the file extension, so I tried changing it from a .csv file to an .xsl file, but that didn't make any difference. Based on some further experimenting, it seems like I can't open any file that should open in excel. I'm not sure if this is correct or not, but I know I was able to open a word document (in Microsoft word) using this strategy. Here is the actual code I am using:

Dim str_folder As String
str_folder = "C:\pe\ICP\Reports\"
Call Shell("explorer.exe " & str_folder, vbNormalFocus)

I really need some help figuring this one out and would really appreciate any input you guys have because I'm sure the problem isn't all that hard to figure out for an experienced VBA programmer.

Thanks in advance - Forest

 

[Domenic]

When I run your code in Excel 2010, the referenced folder/directory opens in Windows Explorer, and I'm able to double-click on any file (.xls, .csv, etc.) to open it. The same should be true for Excel 2007 and earlier versions.

 

[fdanford]

Thanks so much for responding and trying the code out. I foolishly left out some of the other details of the program that seem to be pertinent since it worked for you (and me - I tried it out by making a new sheet with just a button, that when clicked just loaded the shell, and when I double-clicked any of the excel files, they opened).

The issue seems to be arising with a class module (maybe?) that I found online to keep track of other excel files when they are opened. The code works great when I load the .csv files using the Workbooks.Open FileName:=File_location method. However, the excel file will not open when a folder is opened via a shell command and the file is double-clicked. The code that I wrote and borrowed is as follows (with RawDataLoader being a user form and AppClass being a Class Module I made):

In ThisWorkbook the code is:

Private Sub Workbook_Open()
Dim AppEvents As New AppClass
Set AppEvents.xlApp = Application

Load RawDataLoader
RawDataLoader.Show

End Sub

In Module1 the code is:

Sub Initialize()
Dim AppEvents As New AppClass

Set AppEvents.xlApp = Application

Load RawDataLoader
RawDataLoader.Show

End Sub

In the UserForm called RawDataLoader the code is:

Private Sub Next_Button_Click()
If FolderBrowser.Value = True Then
Dim str_folder As String
str_folder = "C:\pe\ICP\Reports\" ' Directory where file should be
Call Shell("explorer.exe " & str_folder, vbNormalFocus)
ElseIf AutoGuess.Value = True Then
Load IntelligentGuess
IntelligentGuess.Show
ElseIf ManualEntry.Value = True Then
Load ManualEnter
ManualEnter.Show
Else
MsgBox "You must select one of the options to proceed."
End If
End Sub

In the Class Module named ClassApp the code is:
Public WithEvents xlApp As Application
Private Sub xlApp_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
Dim i As Integer
Dim NewFileName As String
Dim WindowName As String

i = 1
NewFileName = Wb.Name

'Need to add code that opens new sheet and outputs the file name
'Allows user to preview the opened file.
'If right, program proceeds, if wrong closes excel file without saving
'Re-opens the appropriate UserForm
End Sub

In the RawDataLoader UserForm the user has 3 radio buttons to select from named FolderBrowser, AutoGuess, and ManualEntry. When the Next button on the form is clicked, it checks to see which one has been selected and then performs the appropriate task. In the case of FolderBrowser the folder is opened via the shell command, but when the user double-clicks the file, it doesn't open it, which prevents me from moving on in the program. In the other two options, a userform is opened that allows the user to open the excel file using the method Workbooks.Open FileName:=File_location. This opens the excel file just fine and interacts with the Class Module ClassApp as it should.

It's not vital, but I really would like to allow the user to browse the folder using the shell method. However, in order to do this, I obviously have to get excel to open the file and then have it interact with the Class module as it should. Any further help in this situation would be awesome. Thanks for your time.

 

[Domenic]

Sorry, I'm not as yet familiar with class modules, so I'll have to defer to others here with more experience.

 

[fdanford]

Darn that's a bummer. Thanks for looking at it Domenic. Do you know if there's maybe another way to go about keeping track of the other excel documents when they're opened that doesn't involve a class module?