Open Excel File with Filedialog

[jadown]

Hello all,

I need a quick fix. I can't get my code to open the file. The file dialog opens but when I select the file nothing happens. What am I missing?

Dim fd As FileDialog
Dim FileName As String
'Dim Workbooks As OfficeDataSourceObject
 
Set fd = Application.FileDialog(msoFileDialogOpen)
'the number of the button chosen
Dim FileChosen As Integer
FileChosen = fd.Show
fd.Title = "Choose workbook"
fd.InitialFileName = "C:\FilePath"
fd.InitialView = msoFileDialogViewList
'show Excel workbooks and macro workbooks
fd.Filters.Clear
fd.Filters.Add "Excel workbooks", "*.xls"
fd.Filters.Add "Excel macros", "*.xlsm"
fd.FilterIndex = 1
fd.ButtonName = "Choose this file"
If FileChosen <> -1 Then
'didn't choose anything (clicked on CANCEL)
MsgBox "No file opened"
Else
'get file, and open it (NAME property
'includes path, which we need)
FileName = fd.SelectedItems(1)
Workbooks.Open (FileName)

Range("C1") = "Cost elem"
Range("D1") = "Cost element descr"
Range("E1") = "Per"
Range("F1") = "Year"
Range("G1") = "RefDocNo"
Range("H1") = "User"
Range("I1") = "Name"
Range("J1") = "PartnerObj"
Range("K1") = "Purchdoc"
Range("L1") = "Descr"
Range("M1") = "Material"
Range("N1") = "Sales doc"
Range("O1") = "Partner order"
Range("P1") = "Postg date"
Range("Q1") = "Value COCurr"
Range("R1") = "Quantity"
Range("S1") = "Quantity2"
End If

 

[MarkK]

The filename you get from the dialog is just the path to the file. You need to crate an Excel application and pass that filename to the Open method of the Documents collection.

"Create Excel Application Object" <-- search term

You might also want to "Require Variable Declaration" <-- search term

cheers,

 

[jadown]

Thanks for the help. I tried this but the problem is that if click the command button after closing/saving excel the button won't work anymore.

Private Sub cmdOpenSaveExcelTest_Click()
On Error GoTo Err_Command8_Click
    Dim oApp As Object
    Set oApp = CreateObject("Excel.Application")
    oApp.Visible = True
    'Only XL 97 supports UserControl Property
    On Error Resume Next
    oApp.UserControl = True
    
Dim fd As FileDialog
Dim FileName As String
'Dim Workbooks As OfficeDataSourceObject
 
Set fd = Application.FileDialog(msoFileDialogOpen)
'the number of the button chosen
Dim FileChosen As Integer
FileChosen = fd.Show
fd.Title = "Choose workbook"
fd.InitialFileName = "C:\FilePath"
fd.InitialView = msoFileDialogViewList
'show Excel workbooks and macro workbooks
fd.Filters.Clear
fd.Filters.Add "Excel workbooks", "*.xls"
fd.Filters.Add "Excel macros", "*.xlsm"
fd.FilterIndex = 1
fd.ButtonName = "Choose this file"
If FileChosen <> -1 Then
'didn't choose anything (clicked on CANCEL)
MsgBox "No file opened"
Else
'get file, and open it (NAME property
'includes path, which we need)
FileName = fd.SelectedItems(1)
Workbooks.Open (FileName)

Range("C1") = "Cost elem"
Range("D1") = "Cost element descr"
Range("E1") = "Per"
Range("F1") = "Year"
Range("G1") = "RefDocNo"
Range("H1") = "User"
Range("I1") = "Name"
Range("J1") = "PartnerObj"
Range("K1") = "Purchdoc"
Range("L1") = "Descr"
Range("M1") = "Material"
Range("N1") = "Sales doc"
Range("O1") = "Partner order"
Range("P1") = "Postg date"
Range("Q1") = "Value COCurr"
Range("R1") = "Quantity"
Range("S1") = "Quantity2"
End If
 
oApp.Parent.Quit
Set oApp = Nothing
Exit_Command8_Click:
    Exit Sub
Err_Command8_Click:
    MsgBox Err.Description
    Resume Exit_Command8_Click

Set fd = Nothing

End Sub

 

[MarkK]

A few things . . .

1) You are using an object called Workbooks, but I don't see where it is declared. Did you Require Variable Declaration? Does it say Option Explicit at the top of your module? If you require variable declaration then objects you haven't declared raise compile errors which makes them easy to find. If you don't require variable declaration and you misspell something, VBA implicitly creates a new variable, and doesn't raise an error. That is hard to find.

2) To open a file in the Excel application you created, you need to pass the filename to the Open method of the Documents collection, so that looks like ...

dim xl as object
dim doc as object
set xl = createobject("Excel.Application")
set doc = xl.documents.open(filename)

3) You might also want to "set a reference to the Excel object model", and then you can declare a variable of type Excel.Application, which shows intellisense, and shows the object model in the object browser, which gives you very rich information about what objects, methods and events are exposed by Excel.

dim xl as New Excel.Application
dim doc as Excel.Document
set doc = xl.documents.open(filename)

4) You also show your FileDialog before you've set any of its properties, and maybe this is what kills execution of you code. Keep your code tidy and preserve indents, otherwise you get bugs like that . . .

Making sense?

 

[jadown]

lagbolt thanks for your help.

I'm trying to understand what you are saying but I still can't get it to work. I haven't wrote code in years. So I guess it's just not clicking. I'm still confused on how to place everything in the code.

 

[MarkK]

OK, so we back up and keep it simple. Did you search for "Require Variable Declaration?" What did you find? What did you do?
Cheers,