Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Until loop with if statement
I am looping through files in a directory with a Do
Until ... Loop. If there is a file name with "div...".xls in it, I need to skip it and go to the next file. Any suggestions would be greatly appreciated! Sandy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Until loop with if statement
Sandy wrote:
I am looping through files in a directory with a Do Until ... Loop. If there is a file name with "div...".xls in it, I need to skip it and go to the next file. Any suggestions would be greatly appreciated! Sandy It's a bit hard to get specific without some of the code you have already written, but it might look something like this: strFileName = <first filename Do If Left(strFileName,3) < "div" Then <process file somehow ... End If strFileName = <set to next filename Loop Until <condition Hope this helps, Matthew |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Until loop with if statement
Thanks for your response!
Note that if I try to use GoTo SKIP_FILE on an if statement, the whole thing fails. The code is: Public Function ExportToTemplates() ' Purpose: export data in database to history data sheet in each template in the templates out directory Dim dbs As Database, rst As Recordset, ls_sql As String Dim xl As Object, Sheet As Object Dim DBPath As String, FileName As String, ls_area As String, ls_msg As String, FilePathName As String Dim CurrentValue As Variant, CurrentField As Variant Dim li_return As Integer, i As Integer, j As Integer, iCols As Integer Dim ls_destination As String On Error GoTo SKIP_FILE Set dbs = DBEngine.Workspaces(0).Databases(0) ls_msg = "Place all spreadsheets in the Templates_Out subdirectory and make sure they are all CLOSED." li_return = MsgBox(ls_msg, vbOKCancel, "Double Check!") If li_return = 1 Then ' establish link to Excel Set xl = CreateObject("Excel.Application") xl.Application.Visible = True ' get list of all xls files in Templates_Out directory DBPath = GetDatabasePath() FileName = Dir(DBPath & "Templates_Out\*.xls") Do Until FileName = "" 'Put If "div" file language in here ' open workbook and set sheet FilePathName = DBPath & "Templates_Out\" & FileName xl.Application.Workbooks.Open FilePathName ' get area code ls_area = xl.Application.Worksheets ("AREA").Range("C1").Value ls_area = ls_area & xl.Application.Worksheets ("AREA").Range("C2").Value xl.Application.Worksheets ("history_data").Activate ' delete data on history_data sheet xl.Application.Worksheets ("history_data").Cells.ClearContents ' create history_data recordset of data to export to excel ls_sql = "SELECT * FROM tbl_history where area_id = '" & ls_area & "' order by vlookup_key" Set rst = dbs.OpenRecordset(ls_sql, DB_OPEN_DYNASET) ' copy history_data from recordset to excel For iCols = 0 To rst.Fields.Count - 1 xl.Application.Worksheets ("history_data").Cells(1, iCols + 1).Value = rst.Fields (iCols).Name Next xl.Application.Worksheets("history_data").Range ("A2").CopyFromRecordset rst ' save, close, and move to _done xl.Application.Workbooks(FileName).Close SaveChanges:=True ls_destination = DBPath & "Templates_Out_Done\" & FileName FileCopy FilePathName, ls_destination Kill FilePathName SKIP_FILE: 'If IsFileOpen(FilePathName) = -1 Then ' xl.Application.Workbooks(FileName).Close SaveChanges:=False 'End If ' get the next file name from the list FileName = Dir() Loop Set Sheet = Nothing xl.Quit Set xl = Nothing DoCmd.SetWarnings True li_return = MsgBox("All Done!", vbOK, "FYI") End If End Function Thanks again! Sandy -----Original Message----- Sandy wrote: I am looping through files in a directory with a Do Until ... Loop. If there is a file name with "div...".xls in it, I need to skip it and go to the next file. Any suggestions would be greatly appreciated! Sandy It's a bit hard to get specific without some of the code you have already written, but it might look something like this: strFileName = <first filename Do If Left(strFileName,3) < "div" Then <process file somehow ... End If strFileName = <set to next filename Loop Until <condition Hope this helps, Matthew . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Until loop with if statement
Sandy wrote:
Thanks for your response! Note that if I try to use GoTo SKIP_FILE on an if statement, the whole thing fails. The code is: Public Function ExportToTemplates() <snip On Error GoTo SKIP_FILE Set dbs = DBEngine.Workspaces(0).Databases(0) <snip FileName = Dir(DBPath & "Templates_Out\*.xls") Do Until FileName = "" 'Put If "div" file language in here If UCase(Left(FileName, 3)) = "DIV" Then GoTo SKIP_FILE Does this line not work for you? An identical functional way would be to have he If UCase(Left(FileName, 3)) = "DIV" Then and a corresponding 'End If' just above the SKIP_FILE line. Of course, you might want to indent the lines within the IF - END IF. Matthew <snip FilePathName = DBPath & "Templates_Out\" & FileName xl.Application.Workbooks.Open FilePathName ' get area code <snip FileCopy FilePathName, ls_destination Kill FilePathName SKIP_FILE: <snip FileName = Dir() Loop <snip End If End Function Thanks again! Sandy -----Original Message----- Sandy wrote: I am looping through files in a directory with a Do Until ... Loop. If there is a file name with "div...".xls in it, I need to skip it and go to the next file. Any suggestions would be greatly appreciated! Sandy It's a bit hard to get specific without some of the code you have already written, but it might look something like this: strFileName = <first filename Do If Left(strFileName,3) < "div" Then <process file somehow ... End If strFileName = <set to next filename Loop Until <condition Hope this helps, Matthew . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Until loop with if statement
Thank you so much!!
-----Original Message----- Sandy wrote: Thanks for your response! Note that if I try to use GoTo SKIP_FILE on an if statement, the whole thing fails. The code is: Public Function ExportToTemplates() <snip On Error GoTo SKIP_FILE Set dbs = DBEngine.Workspaces(0).Databases(0) <snip FileName = Dir(DBPath & "Templates_Out\*.xls") Do Until FileName = "" 'Put If "div" file language in here If UCase(Left(FileName, 3)) = "DIV" Then GoTo SKIP_FILE Does this line not work for you? An identical functional way would be to have he If UCase(Left(FileName, 3)) = "DIV" Then and a corresponding 'End If' just above the SKIP_FILE line. Of course, you might want to indent the lines within the IF - END IF. Matthew <snip FilePathName = DBPath & "Templates_Out\" & FileName xl.Application.Workbooks.Open FilePathName ' get area code <snip FileCopy FilePathName, ls_destination Kill FilePathName SKIP_FILE: <snip FileName = Dir() Loop <snip End If End Function Thanks again! Sandy -----Original Message----- Sandy wrote: I am looping through files in a directory with a Do Until ... Loop. If there is a file name with "div...".xls in it, I need to skip it and go to the next file. Any suggestions would be greatly appreciated! Sandy It's a bit hard to get specific without some of the code you have already written, but it might look something like this: strFileName = <first filename Do If Left(strFileName,3) < "div" Then <process file somehow ... End If strFileName = <set to next filename Loop Until <condition Hope this helps, Matthew . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
for loop with if statement | Excel Worksheet Functions | |||
Find loop doesn't loop | Excel Discussion (Misc queries) | |||
Loop Statement through If Not IsEmpty Then Paste into Destination | Excel Discussion (Misc queries) | |||
On Error Resume Next (when next statement is Do Loop ...) | Excel Discussion (Misc queries) | |||
If statement - Loop? | Excel Discussion (Misc queries) |