Open File Dialog 2

[MacroAlan]

I followed the thread thread705-1181496 and thought I knew what I was doing but cannot make the file actually open so that I can do anything with it. Did I miss an instruction?

After I get it open, I will be parsing the headings into a form we have created.

I’ve done Excel VBA for more than 10 years but Access has me stumped.

Alan

 

[traingamer]

What are you actually getting? An error message?

(This isn't the MacroAlan for whom AB has a double meaning, is it?)

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[MacroAlan]

Das is me; new gig!

Not getting an error message; just not opening the file.

 

[traingamer]

Some of this is from a (gasp ) real-life conversation with Alan:

The function you're looking at returns a string with the pathname of an excel file. Still determining what specifically needs to be done with Excel file. Will post answer(s) later.

 

[PHV]

What is your actual code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MacroAlan]

After all this, my friends have suggested that I pass control to Excel. Do the steps I need over there with the spreadsheets and bring just the elements that I need back into Access.

Once I work it out, I will post the whole code block.

Thanks everyone!

 

[MacroAlan]

Ok folks. I know I am not crazy. Here is the code I have so far:

This is in the form code behind…

Option Compare Database

Private Sub cmdBrowse_Click()
    GetExcelSpread  [red]'run macro to open Excel[/red]
End Sub

and this is in my modRunExcel code…

Sub GetExcelSpread()
    Dim xlApp As excel.Application
    Dim xlBook As excel.Workbook
    Dim xlSheet As excel.worksheet
    Dim Filt As String, fIndex As Integer
    Dim Title As String, FileNamer As Variant
    Dim ExcelRunning As Boolean
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = False   [red]'Work in the background[/red]
    Filt = "Text files (*.txt),*.txt," & _
        "Excel files (*.xls),*.xls," & _
        "Comma Separated (*.csv),*.csv," & _
        "All files (*.*),*.*"
    fIndex = 2  [red]'default to xls[/red]
    Title = "Select a file to Import"
    FileNamer = xlApp.GetOpenFileName(FileFilter:=Filt, _
        FilterIndex:=fIndex, Title:=Title)
    xlBook.Open FileNamer [red]'open the file[/red]
    With xlBook
        .activesheet.range("A1").select 'Select
    End With
End Sub

When I click the Browse button on my form nothing happens. I have tried F8 stepping thru the code and nothing happens.

Somehow, I need to open a spreadsheet, gather the headings in row 1 and apply them to a series of dropdowns on my Access form.


Alan

 

[traingamer]

What happens if you make

xlApp.Visible = true

instead of 'false'?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[PHV]

furthermore, replace this:
xlBook.Open FileNamer
with this:
Set xlBook = xlApp.Workbooks.Open(FileNamer)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MacroAlan]

What was keeping it from running? Practically a newbie mistake. Must go to Tools | References and choose "Microsoft Excel ## Object Library" so that it can open Excel.