passing an open file variable to excel.workbook

[token_remedie]

so I'm trying to use filedialog to open a file then pass that filename to dim myxlworkbook as excel.application so that it populates a combo box of the sheetnames which ill then take that selection of sheetnames for docmd.transferspreadsheet import.

anyway I'm getting runtime error 91 Object Variable or With block variable not set.
maybe i've been staring at it too long but I thought I did that?
I should mention I'm still learning.

Dim fd As FileDialog
    Dim strfile As String
    Dim strsearchpath As String
    Dim vrtSelectedItem As Variant
    Dim strSql As String
       
    Dim MyXLApp As Excel.Application
    Dim MyXLWorkBook As Excel.Workbook
        
 Set myDialog = Application.FileDialog(msoFileDialogOpen)
 'searchpath is set at c:\ for now, needs to be changed when live
 strsearchpath = "c:\"
 
 With myDialog
 
    .AllowMultiSelect = False
    .Filters.Add "Excel Files", "*.xlsx", 1
     .Title = "locate files"
     .InitialFileName = strsearchpath
     
DoCmd.SetWarnings False

If .Show = True Then
    For Each vrtSelectedItem In .SelectedItems
     MsgBox "file chosen = " & vrtSelectedItem
     
     Set MyXLApp = New Excel.Application
     Set MyXlWorkBookk = MyXLApp.Workbooks.Open(vrtSelectedItem)
     
     Next
     
     For i = 1 To MyXLWorkBook.Sheets.Count
     Set myXLSheet = MyXLWorkBook.Sheets(i)
     myXLSheet.Select
     Combo4.AddItem myXLSheet.Name
     Next i

 

[boblarson]

You have this:

Dim fd As FileDialog


But then you try to use this:

Set myDialog = Application.FileDialog(msoFileDialogOpen)


If you were to change myDialog to fd everywhere it is used, see if that helps.

 

[token_remedie]

you're my new hero. I haven't tried it yet but I think that will work. I was just mapping it out and really I need to store that filename somewhere for later on, because when the combobox worksheet is selected i want to import it and run a bunch of queries.

is that possible?

 

[token_remedie]

yep that worked.

here's the code for anyone interested

With fd

.AllowMultiSelect = False
.Filters.Add "Excel Files", "*.xlsx", 1
.Title = "locate files"
.InitialFileName = strsearchpath

DoCmd.SetWarnings False

If .Show = True Then
For Each vrtSelectedItem In .SelectedItems
MsgBox "file chosen = " & vrtSelectedItem

Set MyXLApp = New Excel.Application
Set MyXLWorkBook = MyXLApp.Workbooks.Open(filename:=vrtSelectedItem)


Next

For i = 1 To MyXLWorkBook.Sheets.Count
Set myXLSheet = MyXLWorkBook.Sheets(i)
myXLSheet.Select
Combo4.AddItem myXLSheet.Name
Next i


still need to work out how I'm going to use that value now from thw combo box to import thanks boblarson

 

[boblarson]

Since you are limiting it to a single file, you can just use what I have below. My changes are shown in red.

    Dim fd As FileDialog
    Dim strfile As String
    [COLOR=black]Dim strsearchpath As String
[/COLOR]    [B][COLOR=red]Dim strPathAndFile As String
[/COLOR][/B]    Dim strSql As String
 
    Dim MyXLApp As Excel.Application
    Dim MyXLWorkBook As Excel.Workbook
 
    Set [B][COLOR=red]fd[/COLOR][/B] = Application.FileDialog(msoFileDialogOpen)

    'searchpath is set at c:\ for now, needs to be changed when live
    strsearchpath = "c:\"
 
    With [B][COLOR=red]fd[/COLOR][/B]
        .AllowMultiSelect = False
        .Filters.Add "Excel Files", "*.xlsx", 1
        .Title = "locate files"
        .InitialFileName = strsearchpath
     [B][COLOR=red]   .Show
[/COLOR][COLOR=red]    End With[/COLOR][/B]
[B][COLOR=#ff0000][/COLOR][/B] 
    [B][COLOR=red]strPathAndFile = fd.SelectedItems(1)[/COLOR][/B]

   [B][COLOR=red] If strPathAndFile <> vbNullString Then[/COLOR][/B]
        MsgBox "file chosen = " & [B][COLOR=#ff0000]strPathAndFile [/COLOR][/B]
[B][COLOR=#ff0000][/COLOR][/B] 
        Set MyXLApp = New Excel.Application
        Set MyXlWorkBookk = MyXLApp.Workbooks.Open([B][COLOR=red]strPathAndFile[/COLOR][/B])
 
        For i = 1 To MyXLWorkBook.Sheets.Count
            Set myXLSheet = MyXLWorkBook.Sheets(i)
            myXLSheet.Select
            Combo4.AddItem myXLSheet.Name
        Next
    [B][COLOR=red]End If
[/COLOR][/B]

 

[boblarson]

yep that worked.

here's the code for anyone interested

First off - you don't need to use the variant and don't need to cycle through selected items if you have multiselect set to false.

Use what I posted in my post above.

Also, what was the

DoCmd.SetWarnings False

for? It is best if you can avoid using those if at all possible.

 

[token_remedie]

the false warnings are there to turn off the append queries saying you're about to append blah blah

so the question now is, if I've loaded the worksheet names into that combo box how do i import that spreadsheet based on which value\worksheet the user selects.

because putting DoCmd.TransferSpreadsheet acImport, , "importtable", "strPathAndFile", True in the afterupdate() for the combo box wont work, I guess what I'm asking is how does access know that the selection is coming from that same file I just imported?

 

[boblarson]

because putting DoCmd.TransferSpreadsheet acImport, , "importtable", "strPathAndFile", True

With variables you don't use quotes:

DoCmd.TransferSpreadsheet acImport, , "importtable", strPathAndFile, True


(also, with the SetWarnings, make sure you have an exit point so that the code can exit normally but will also exit at the same point if there is an error and in that exit point put the code to turn the warnings back on)

 

[token_remedie]

is that supposed to go in the combobox afterupdate()?

 

[boblarson]

is that supposed to go in the combobox afterupdate()?

Is what supposed to go in the combobox afterupdate?

 

[token_remedie]

well, I want access to import the worksheet based on what the user selects so if I put DoCmd.TransferSpreadsheet acImport, , "importtable", strPathAndFile, True in combo4_Afterupdate() it says Runtime error 2522 the action or method required a File Name argument.

That's what I mean when I say how does access know the file we just imported with the previous button, or if i put DoCmd.TransferSpreadsheet acImport, , "importtable", strPathAndFile, True in the button's code then how do I get it to pause while the user makes their selection from the combo box?

 

[boblarson]

well, I want access to import the worksheet based on what the user selects so if I put DoCmd.TransferSpreadsheet acImport, , "importtable", strPathAndFile, True in combo4_Afterupdate() it says Runtime error 2522 the action or method required a File Name argument.

That's what I mean when I say how does access know the file we just imported with the previous button, or if i put DoCmd.TransferSpreadsheet acImport, , "importtable", strPathAndFile, True in the button's code then how do I get it to pause while the user makes their selection from the combo box?

Okay, you can use strPathAndFile if it is in the same procedure as the selection of the Excel file from the dialog. If it isn't in the same procedure and you are wanting to let the user select the worksheet from the combo, then you might want to put

Private strPathAndFile As String

in the General Declarations section of the form's module and then you can use it later in the combo box's After Update event. What code do you have in the After Update event?

 

[token_remedie]

that's so awesome! I had no idea I could do that


this is all I have in there"

Private Sub Combo4_AfterUpdate()

DoCmd.TransferSpreadsheet acImport, , "importtable", strPathAndFile, True


End Sub

 

[token_remedie]

for some reason everything has stopped working now, so I took it back a few steps to here:
and it's not getting to the msgbox with the strPathAndFile, all I did was add the global declarations and then that still failed :S

Option Compare Database

Private Sub Command0_Click()
    Dim fd As FileDialog
    Dim strfile As String
    Dim strsearchpath As String
    Dim strPathAndFile  As String
    Dim strSql As String
       
    Dim MyXLApp As Excel.Application
    Dim MyXLWorkBook As Excel.Workbook
       
      
 Set fd = Application.FileDialog(msoFileDialogOpen)
 'searchpath is set at c:\ for now, needs to be changed when live
 strsearchpath = "c:\"
 
 With fd
 
.AllowMultiSelect = False
    .Filters.Add "Excel Files", "*.xlsx", 1
     .Title = "locate files"
     .InitialFileName = strsearchpath
     .Show
     End With
     
If strPathAndFile <> vbNullString Then
      MsgBox "file chosen = " & strPathAndFile
     
     
     Set MyXLApp = New Excel.Application
    Set MyXLWorkBook = MyXLApp.Workbooks.Open(strPathAndFile)
    
     For i = 1 To MyXLWorkBook.Sheets.Count
     Set myXLSheet = MyXLWorkBook.Sheets(i)
     myXLSheet.Select
     Combo4.AddItem myXLSheet.Name
     Next

     End If
          
 
 End Sub

any ideas?

 

[token_remedie]

oh wow, I've been staring at this too long, somewhere along the way I deleted:
strPathAndFile = fd.SelectedItems(1)

 

[boblarson]

You also need to remove

Dim strPathAndFile As String

as it would override your module level variable.