Go sequentialy through a list 1

[Triacona]

Dear All,

I have an interesting dilema.

I have a List(ListFileName) generated by a TableFileName)

I have code that imports my excel documents into Access.

But I want to go through the list sequentialy, as not to have to click on each one,so I want my code to cycle through the list until it reaches the end.

I have used a While loop?(good idea?)
I have tested it with out the loop and it works fine, importing the excel spreadsheets into tables.

But if I add the loop below:

Private Sub ImportAllExcelSpreadsheets_Click()
On Error GoTo Err_ImportAllExcelSpreadsheets_Click


Dim ExcelDoc As String
Dim oApp As Object
Dim sFullPath As String
Dim i As Integer


Set oApp = CreateObject("Excel.Application")



While i <> 34

    i = i + 1
                      'ListFileName.ItemsSelected (i)
    ListFileName.ListIndex (i)
                      'Controls.(ListFileName).ListIndex (i)
    
    oApp.Visible = True
    
    sFullPath = txtPath & ListFileName & ".xls"
    
    With oApp
        .Visible = True
        .Workbooks.Open (sFullPath)
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, ListFileName, sFullPath, True
        
    End With
    
Wend
  
Exit_ImportAllExcelSpreadsheets_Click:
    Exit Sub

Err_ImportAllExcelSpreadsheets_Click:
    MsgBox Err.Description
    Resume Exit_ImportAllExcelSpreadsheets_Click
    
End Sub

It gives me an error:

Compile Error:
Invalid use of property

It highlights ListFileName.ListIndex (i)

I have removed that and it just repeats importing the spreadsheet I picked in the list for 34 times!

How do I get my code to move through the list and import each item.

Any help would be really appreciated!

Kind regards

Triacona

 

[PHV]

A starting point:

For i = 0 To ListFileName.ListCount - 1
  sFullPath = txtPath & ListFileName.ItemData(i) & ".xls"
  MsgBox sFullPath
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Triacona]

Dear PHV,

Thank you very much!

I added one more line and it works brilliantly!!

Private Sub ImportAllExcelSpreadsheets_Click()
On Error GoTo Err_ImportAllExcelSpreadsheets_Click


Dim ExcelDoc As String
Dim oApp As Object
Dim sFullPath As String
Dim i As Integer


Set oApp = CreateObject("Excel.Application")
 
    For i = 0 To ListFileName.ListCount - 1
    
        sFullPath = txtPath & ListFileName.ItemData(i) & ".xls"
        MsgBox sFullPath
       [COLOR=red yellow][b] DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, ListFileName.ItemData(i), sFullPath, True[/b][/color]
        
    Next

 
Exit_ImportAllExcelSpreadsheets_Click:
    Exit Sub

Err_ImportAllExcelSpreadsheets_Click:
    MsgBox Err.Description
    Resume Exit_ImportAllExcelSpreadsheets_Click
    
End Sub

Thanks again!