using public variable with excel.application

[token_remedie]

hey, what am I doing wrong here?

so far it lets the user select a file, adds the worksheets to a combobox
now on the afterupdate() in the combo box i want to docmd.transfer spreadsheet based on the selection in the combobox. here's the code so far:

Private strPathAndFile As String ' this is in general declarations

Option Compare Database

Private Sub Combo4_AfterUpdate()

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

End Sub

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
     
     strPathAndFile = fd.SelectedItems(1)


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


Private Sub Form_Close()

Combo4.ListItemsEditForm = ""

End Sub

 

[token_remedie]

ok in the update for the combo box I've got:

Private Sub Combo4_AfterUpdate()
Dim MyRange As String
MyRange = Combo4.Text
MsgBox "" + strPathAndFile + MyRange
'DoCmd.TransferSpreadsheet acImport, , "importtable", strPathAndFile, True, "MyRange"

which outputs the range as text, that's fine for now right? I still can't seem to make it find that file name though

 

[boblarson]

As I just wrote in your other thread - you need to remove this line:

Dim strPathAndFile As String

from your Command0_Click event.

(plus you should really name your controls something more meaningful BEFORE you code in the events. If you rename your control you will have to move the code to that same click event but the new name)

 

[token_remedie]

yeah that worked, and I totally agree about the naming conventions. This whole thing started as a test to see what I could do with it, and what would be possible, and it's looking like anything is possible with the more I learn.

The issue is now that
Private Sub Combo4_AfterUpdate()
Dim MyRange As Range

wont work as a range but if i put
Private Sub Combo4_AfterUpdate()
Dim MyRange As string then it doesn't find the worksheet :S

hope that makes sense

 

[token_remedie]

worked it out

Private Sub Combo4_AfterUpdate()
Dim MyRange As String
MyRange = Combo4.Text
MsgBox "" + MyRange + "" + strPathAndFile
DoCmd.TransferSpreadsheet acImport, , "importtable", strPathAndFile, True

End Sub

Thank you so much for your help it's very much appreciated

 

[boblarson]

The range, when referencing it, is different than just the sheet name. Also, again you do NOT surround VARIABLES with quotes. They do not have quotes. So if the sheet is selected by the combo, you would have to use:

Private Sub Combo4_AfterUpdate()
Dim MyRange As String
MyRange = [B][COLOR=red]Combo4 & "!A:ZZ"[/COLOR][/B]
MsgBox "" & strPathAndFile & MyRange
DoCmd.TransferSpreadsheet acImport, , "importtable", strPathAndFile, True, MyRange

 

[boblarson]

What you posted will not get the selected sheet. You aren't getting anything but the first sheet. You need to edit to what I put in red above.

Also, you should use the ampersand (&) for concatenation and not the + sign. Using the + sign can have unintended side effects in Access depending on what you are concatenating. It isn't like SQL Server.

 

[token_remedie]

for some reason that's actually stopped working :S
the message box opens properly with the right file name and myrange is correct and then it says it can't find it and errors out

 

[boblarson]

Strange. I tested it myself before posting and it worked for me. Make sure the combo is returning exactly what you think it is supposed to.

Do you have this line exactly as I have it?
MyRange = Combo4 & "!A:ZZ"

If so, you should also use Me.Combo4 instead of just Combo4. Put a debug.print just after that line and then run it and copy the result from the Immediate Window and post here.

 

[token_remedie]

I had a heap of EXCEL processes open so I restarted and tried again, same result. it's not getting anything in the immediate window, I'm guessing because it's not getting to the debug.print. the error is:

Run-time error '3011':
The Microsoft Access database engine could not fine the object 'Master$A:ZZ'. Make sure the object exists and that you spell its name and the path name correctly. If 'Master$A:ZZ' is now a local object, check your network connection or contact the server administrator.

The file is on my desktop, but eventually it will be over a network connection.

 

[boblarson]

Where did you put the debug.print line? Can you post the code you currently have right now, after the changes?

The heap of Excel processes is probably due to the fact that you don't have code in there to close and quit Excel (at least I don't see it in the code you originally posted).

 

[token_remedie]

that's the whole lot.
I came to the realisation that it needs to kill excel processes this morning after the restart, I haven't put that in yet though, might try and get it working first.

Private strPathAndFile As String

Option Compare Database
Private Sub Combo4_AfterUpdate()
Dim MyRange As String
MyRange = Me.Combo4 & "!A:ZZ"
Debug.Print
MsgBox "" & strPathAndFile & MyRange
DoCmd.TransferSpreadsheet acImport, , "importtable", strPathAndFile, True, MyRange
End Sub

Private Sub Command0_Click()
    Dim fd As FileDialog
    Dim strfile As String
    Dim strsearchpath 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
     
     strPathAndFile = fd.SelectedItems(1)


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
          MsgBox strPathAndFile

 End Sub

 

[boblarson]

You can get rid of this:
myXLSheet.Select

as it isn't necessary when you are adding the names to the combo.

Also, you need to add just at the end of that click event just before the End Sub:

[B]MyXLWorkbook.Close False[/B]
[B]MyXLApp.Quit[/B]
 
[B]Set MyXLApp = Nothing[/B]

It may not be able to import since the workbook is still open. So, see if the addition of the closing of the workbook and quitting of Excel will help.

 

[token_remedie]

nope, so weird, it worked yesterday and all of a sudden it's not, the new code isn't helping as it's just not getting that far

 

[token_remedie]

i worked it out, if I close excel at the end of the command0click it retains the filename variable in the public even though excel is closed, and allows import of it