Whille loop not going to next script text file.

Posted on 2011-09-20
Medium Priority
Last Modified: 2012-05-12
I think Its me, but the below simply repeatedly loops through and gets only the first script.  I am a little puzzled as to how to tell it to go to the next script text file, it is my understand the While should do it, but I think I messed up teh code and can't get it to work.
Private Sub Recovery()
'Recovers objects for a specific database.
Dim txtPath
Dim txtFile
Dim strObjectName As String

txtPath = "K:\DatabaseDevelopment\DBScripts_RegE\"
txtFile = Dir(txtPath & "*.txt")

While txtFile <> ""
   Select Case Left(txtFile, InStr(txtFile, "_") - 1)
         Case "Form"
            strObjectName = Left(Mid(txtFile, InStr(1, txtFile, "_") + 1), InStr(1, Mid(txtFile, InStr(1, txtFile, "_") + 1), ".") - 1)
            Application.LoadFromText acForm, strObjectName, txtPath & txtFile
            MsgBox "Form: " & strObjectName & " has been imported.", vbOKOnly
         Case "Table"

   End Select

End Sub

Open in new window

Question by:ssmith94015
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 total points
ID: 36568231

While txtFile <> ""
   Select Case Left(txtFile, InStr(txtFile, "_") - 1)
         Case "Form"
            strObjectName = Left(Mid(txtFile, InStr(1, txtFile, "_") + 1), InStr(1, Mid(txtFile, InStr(1, txtFile, "_") + 1), ".") - 1)
            Application.LoadFromText acForm, strObjectName, txtPath & txtFile
            MsgBox "Form: " & strObjectName & " has been imported.", vbOKOnly
         Case "Table"

   End Select

     txtFile=dir  'ADD this
LVL 58

Expert Comment

ID: 36568306
have a look at this link to get more info on Dir() usage:


MS Access: Dir Function : Dir [( path [, attributes ] ) ]

Dir function returns the first filename that matches the pathname and attributes specified. To retrieve additional filenames that match pathname and attributes, call Dir again with no arguments.


Author Closing Comment

ID: 36568361
Cap, that was it.  Thank you both.


Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question