Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 43
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 17
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 43
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 17
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 43
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
for loop with if statement Steve Excel Worksheet Functions 1 February 17th 10 08:56 PM
Find loop doesn't loop JSnow Excel Discussion (Misc queries) 2 June 24th 09 08:28 PM
Loop Statement through If Not IsEmpty Then Paste into Destination Dandelo Excel Discussion (Misc queries) 7 July 15th 08 10:29 PM
On Error Resume Next (when next statement is Do Loop ...) EagleOne Excel Discussion (Misc queries) 2 September 26th 06 03:26 PM
If statement - Loop? George Excel Discussion (Misc queries) 1 March 14th 06 08:06 AM


All times are GMT +1. The time now is 09:54 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"