How to Skip one cycle of FOR loop

Question

I am importing multiple logs to the DB and process them monthly. My requirement is to skip the log files which are already imported once. I am using a 'For' loop for importing the selected files. If anyone came across these kind of requirements in the MS access vba, please help.

I am using 'Go To' as work around but doesn't feel like a professional approach.

Dim txtImportFileName, txtImportedTbleName, txtImportRange, txtImportedLogTbleName, txtSelectedFiles() As String
   Dim intNumOfFiles, intX, intNumOfRecords As Integer
   intNumOfFiles = mFilePicker("Please select the Log files", "c:\temp\", "Excel files", "*.xlsx", txtSelectedFiles)
   txtImportedTbleName = "tmp_Log"
   txtImportedLogTbleName = "031_Log"
   txtImportRange = ""
      If Confirmation("Please confirm/ご確認ください", "Do you want to import " & intNumOfFiles & "Files?") Then
         For intX = 1 To intNumOfFiles
            txtImportFileName = txtSelectedFiles(intX)
            txtSQLcmd = "SELECT [00_ImportedFiles].FileName FROM 00_ImportedFiles "
            txtSQLcmd = txtSQLcmd & "WHERE ((([00_ImportedFiles].FileName) ALike '" & txtImportFileName & "'));"
            intNumOfRecords = CountQueryRecords(txtSQLcmd) 'calling a function to count the number of records in the Imported File list table
            If (intNumOfRecords > 0) Then
            Debug.Print intNumOfRecords
            GoTo Skip
            End If
            Call ImportFromExcel(txtImportFileName, txtImportedTbleName, txtImportRange)
            'Call Delete_Tables(txtImportedLogTbleName)
            txtSQLcmd = "SELECT F1, F2, F3, F4, F5, F6 INTO " & txtImportedLogTbleName & " "
            txtSQLcmd = txtSQLcmd & "FROM " & txtImportedTbleName & " "
            txtSQLcmd = txtSQLcmd & "WHERE (((F1)<>'日付') AND ((F2)<>''));"
            Call ExecuteSQLcmd(txtSQLcmd)

            Call Delete_Tables(txtImportedTbleName)
Skip:
         Next intX

Could someone propose a better/formal way of doing this?

Avoid labels (Skip:) in code except for error handling . Just reverse your condition (`If (intNumOfRecords > 0) Then`) to `If (intNumOfRecords = 0) Then .Call Delete_Tables(txtImportedTbleName) Else Debug.Print intNumOfRecords End If Next intX`.: If youi don't want tp rely on filename only, you can hash the file (only content) and store that hash to compare to it. — ComputerVersteher, Sep 05 '19 at 00:54
Btw: onlyintNumOfRecords`` is an Integer and`txtSelectedFiles()`is as String array. all other variables are dimed as`Variant`(no type specified). Call is obsolete, you can use functions like subs. Just omit the parenthesis`Delete_Tables( xtImportedTbleName` — ComputerVersteher, Sep 05 '19 at 01:00
@ ComputerVersteher: Do we have any code available to hash the input file(txt, excel) from access vba? "you can hash the file (only content) and store that hash to compare to it.": Looks like a better idea. — Oki Toma, Sep 05 '19 at 04:35
You can nest an additional `Do … Loop` in a neat way, to simulate a kind of `Continue`. See my answer here: https://stackoverflow.com/questions/8680640/vba-how-to-conditionally-skip-a-for-loop-iteration/48984124#48984124 — AHeyne, Sep 05 '19 at 05:59

score 1 · Answer 1 · answered Sep 06 '19 at 00:27

Well, given that the lines to skip is not all that long? then I would go with this:

intNumOfRecords = CountQueryRecords(txtSQLcmd) 'calling a function to count the number of records in the Imported File list table
If (intNumOfRecords =  0) Then
    Call ImportFromExcel(txtImportFileName, txtImportedTbleName, txtImportRange)
    'Call Delete_Tables(txtImportedLogTbleName)
    txtSQLcmd = "SELECT F1, F2, F3, F4, F5, F6 INTO " & txtImportedLogTbleName & " "
    txtSQLcmd = txtSQLcmd & "FROM " & txtImportedTbleName & " "
    txtSQLcmd = txtSQLcmd & "WHERE (((F1)<>'日付') AND ((F2)<>''));"
    Call ExecuteSQLcmd(txtSQLcmd)

    Call Delete_Tables(txtImportedTbleName)
End If

So, just use a if/then for the block of code to run, and use the reverse of the condition to have the if/then block execute.

score 0 · Answer 2 · answered Sep 08 '19 at 18:36

0

Just use a boolean something like this :

Dim blnFirstLineSkipped as Boolean
blnFirstLineSkipped = False
For i =1 to intNumOfFiles
If blnFirstLineSkipped Then
'Normal Code
else
blnFirstLineSkipped =True
End if
Next

answered Sep 08 '19 at 18:36

John

974
8
16

How to Skip one cycle of FOR loop

2 Answers2