Results 1 to 6 of 6

Thread: create loop

  1. #1
    Join Date
    Oct 2003
    Posts
    61

    Question Unanswered: create loop

    I have a form on which i have a combobox showing me all counterparties with wich we do trades. I have to send to all of these counterparties an excel file with open trades as of 31-12-2004.

    I already built a solution in which i have to select the counterparty from a combobox. Then I have to press a button and an excel file will be created with the name of the counterparty and the correct information (through query that is also connected to input of combobox). However since we have over a hundred counterparties i have to do this over and over again. I therefore want to create some kind of loop so that all the excel files will be created by just one press on the button. What i have know is simply the following:

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "rxf", "c:\temp\" & [Forms]![frm_run_macro]![Combo0] & ".xls", 1

    where RXF is the query selecting the correct information and combo0 holds the name of the counterparty.

    Does anybody know how to create a loop that will go to the first possible value in the combobox, create an excel, then go to the second possible value in the combobox, create an excel and so on?
    Nobody dies a virgin because life screws us all!

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    this little piece of code shows how to get the text of every member of a combobox
    Code:
    Private Sub Command2_Click()
    Dim i As Integer
    
        For i = 0 To Combo0.ListCount - 1
            Debug.Print Combo0.ItemData(itm)
        Next i
    End Sub
    HTH
    Dave

  3. #3
    Join Date
    Oct 2003
    Posts
    61
    Quote Originally Posted by DavidCoutts
    this little piece of code shows how to get the text of every member of a combobox
    Code:
    Private Sub Command2_Click()
    Dim i As Integer
    
        For i = 0 To Combo0.ListCount - 1
            Debug.Print Combo0.ItemData(itm)
        Next i
    End Sub
    HTH
    Dave
    And how do implement this in the code i already have being:

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "rxf", "c:\temp\" & [Forms]![frm_run_macro]![Combo0] & ".xls", 1

    so that it will create excel file for every value that is possible in the combobox
    Nobody dies a virgin because life screws us all!

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Code:
    Private Sub Command2_Click()
    Dim i As Integer
    
        For i = 0 To Combo0.ListCount - 1
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "rxf", "c:\temp\" & Combo0.ItemData(i) & ".xls", 1
        Next i
    End Sub
    as above

  5. #5
    Join Date
    Oct 2003
    Posts
    61

    Unhappy

    Quote Originally Posted by DavidCoutts
    Code:
    Private Sub Command2_Click()
    Dim i As Integer
    
        For i = 0 To Combo0.ListCount - 1
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "rxf", "c:\temp\" & Combo0.ItemData(i) & ".xls", 1
        Next i
    End Sub
    as above
    Thanks very much David. It almost works except for one thing: All the excel files that have been created are empty. This is because there is a parameter in the query RXF that looks what is in the combobox. Apparently while running the loop nothing is there therefore the query returns no records and thus empty excel files.

    Do you know how to fix this??
    Nobody dies a virgin because life screws us all!

  6. #6
    Join Date
    Oct 2003
    Posts
    61
    I think i figured it out and that this should do the trick



    Dim i As Integer

    For i = 0 To Combo0.ListCount - 1
    Combo0 = Combo0.ItemData(i)
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "rxf", "c:\temp\" & Combo0.ItemData(i) & ".xls", 1
    Next i

    still many thanks!
    Nobody dies a virgin because life screws us all!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •