New to LOOPs

[benkingery]

I have a situation where I'd like to import multiple files into an access database. Most of the VBA I'm okay with, I'm just new to using loops, so I'm hoping someone has a good ability to help me with looping this function.



I'd like to start out by opening a dialog box that allows the user to open a file, like this:

Dim dlgOpen As FileDialog
  Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
  dlgOpen.InitialFileName = "Y:\Access Databases\Import Files to CRM DB\CSN"
  dlgOpen.Show
  DoCmd.TransferText acImportDelim, "CSSalesTransactions", "ORDERS_CSN_OrderBuffer", dlgOpen.SelectedItems(1)

Pretty easy... After this first function runs, I'd like to ask the user if there are any additional files to be imported. If the answer is "Yes" then I'd like to do this very same thing again. If the answer is no, then I'd like to proceed to my next step and end the loop.



Any help is greatly appreciated.



Thanks in advance.

 

[pbaldy]

Put this before the Set line:

GetAnother:

Add a Yes/No message box after the import asking if the user wants to do another. Test for them answering Yes and if so:

GoTo GetAnother

 

[benkingery]

Do I have to DIM GetAnother as something? I'm getting a Compile error: Sub or Function not defined.

Here is what I put in:

Dim dlgOpen As FileDialog
  GetAnother
  Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
  dlgOpen.InitialFileName = "Y:\Access Databases\Import Files to CRM DB\CSN"
  dlgOpen.Show
  DoCmd.TransferText acImportDelim, "CSSalesTransactions", "ORDERS_CSN_OrderBuffer", dlgOpen.SelectedItems(1)
  MoreFiles = MsgBox("Do you have more files to import?", vbYesNo, "Import")
        If MoreFiles = vbYes Then
            GoTo GetAnother
        End If
        
        If MoreFiles = vbNo Then
            MsgBox ("XXXX")
            'DoCmd.RunSQL "XXXX"
        End If

 

[benkingery]

Oops. Nevermind. I forgot the ":".

Thanks for the help. I hope you are making a lot of money out there. You are an absolute vba genius. Thanks as always.

 

[pbaldy]

I wish I was a genius! Happy to help.