Solved

VB Automation3

Posted on 2007-03-23
37
200 Views
Last Modified: 2010-04-30
I currently have a number of task manager auto schedulers that open up spreadsheets and databases. What I would now like to do is to have another task scheduler that opens up a text file that contains two lines of code; the first has the date and the second a single character that should be deleted, and checks the date to see if it is current. If this is the case then a vbs file that opens up a database should be initiated. Please note that I already have code in excel vb that can open up and check the date in the text file as well as delete the character in the second row.
0
Comment
Question by:f19l
  • 19
  • 18
37 Comments
 
LVL 67

Expert Comment

by:sirbounty
ID: 18781492
Can you post the file with the two lines?
0
 

Author Comment

by:f19l
ID: 18781601
1286320MAR07 1286219MAR07
o

Above is an example of the data contained within the text file. The first line shows two pairs of data; the first part of the dataline is a code representing the date concerned, the second part represents the date itself. Please note that if the date is for before the 10th of any month then a space will present between the code and the date. The second line character is actually a square but is represented by the letter o here.

Below is the excel VB code to open the text file, check the date and delete the character. Please note that if the first date and the second date are not for today and yesterday respectively then a meeasge box should appear making note of this fact and the process should stop.

I will leave this with you now and get back into contact with you on Monday. Have a good weekend.
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18791886
Okay, I've looked over this again and I'm not sure what you want accomplished.
You want the first date into a variable and then do something with the o on the 2nd line...?
0
 

Author Comment

by:f19l
ID: 18791915
What I want to do is to check that the second date in the first row of the text file, in the above example case represented by the "19MAR07", is always equal to the current date. If it is not then a message box should appear stating this fact and the process should stop. If the second date matches with the current date then the next step of the process is to delete any character that appears in the second row/line of the text file (this should always be just one character, a square).

Once the above has been done, assuming, that the date is correct, the text file should be closed and saved and a vbs file that opens up an access database, which has already been created, should be initiated.
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18791934
Delete the character 'from' the same file (just resave it without it?)
0
 

Author Comment

by:f19l
ID: 18791939
Yes.
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18791946
Would something like this accomplish it for you?


Dim objFSO: Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objShell: Set objShell = CreateObject("Wscript.shell")
strFile="C:\YourSourceFile.txt"
Dim objFile: Set objFile = objFSO.OpenTextFile(strFile)
arrDates = Split(objFile.ReadLine, " ")

If Right(arrDates(1), 7) = Day(Date) & UCase(Left(MonthName(Month(Date)), 3)) & Year(Date) Then
    objFile.Close
    Set objFile = objFSO.CreateTextFile(strFile)
    objFile.WriteLine arrDates(0) & " " & arrDates(1)
    objFile.Close
    Set objFile = Nothing
    objShell.Run "C:\OtherScript.vbs"
    Set objFSO = Nothing
    Set objShell = Nothing
    wscript.quit
Else
    MsgBox "Date is not equal to today's date."
    wscript.quit
End If
0
 

Author Comment

by:f19l
ID: 18791958
But beware that the date format in the text file follows a "DMMMYY" format, which means that if the date was the 5th March 2007 then instead of showing 05MAR07 the text file will show 5MAR07, which means that there will be a space between the date and number code e.g. using the above example:-

12863 4MAR07 12862 5MAR07.
0
 

Author Comment

by:f19l
ID: 18791968
I am attaching a VB code that shows how I deal with the date issue. I am not sure if I have already attached it before.

Sub textfile()
  Dim sTemp As String
  Dim sTemp2 As String
  Dim sTemp3 As String
  Dim dTaskID As Double
  Dim FileName As String
  Dim FileNumber As Integer

 
  Open "Y:\Middle Office\AS400\DATA\tabtb10.txt" For Input As #1
  While Not EOF(1)
  Line Input #1, sTemp
 
  If Left(Right(sTemp, 7), 1) <> " " Then
    If Format(Right(sTemp, 7), "DDMMMYY") = UCase(Format(Workbooks("Daily Desktop Procedures.xls").Sheets("Calendar").Range("I61").Value, "DDMMMYY")) Or _
    Format(Right(sTemp, 7), "DDMMMYY") = UCase(Format(Workbooks("Daily Desktop Procedures.xls").Sheets("Calendar").Range("I62").Value, "DDMMMYY")) Then
        If Format(Right(sTemp, 7), "DDMMMYY") <> UCase(Format(Workbooks("Daily Desktop Procedures.xls").Sheets("Calendar").Range("I61").Value, "DDMMMYY")) Then
            MsgBox "Please check the Midas date as it may not have been updated!!!", vbOKOnly + vbExclamation, "OLD MIDAS BATCH DATA!!!"
            Close #1
            End
        End If
    End If
  Else
    If Format(Right(sTemp, 6), "DDMMMYY") = UCase(Format(Workbooks("Daily Desktop Procedures.xls").Sheets("Calendar").Range("I61").Value, "DMMMYY")) Or _
    Format(Right(sTemp, 6), "DDMMMYY") = UCase(Format(Workbooks("Daily Desktop Procedures.xls").Sheets("Calendar").Range("I62").Value, "DMMMYY")) Then
        If Format(Right(sTemp, 6), "DDMMMYY") <> UCase(Format(Workbooks("Daily Desktop Procedures.xls").Sheets("Calendar").Range("I61").Value, "DMMMYY")) Then
            MsgBox "Please check the Midas date as it may not have been updated!!!", vbOKOnly + vbExclamation, "OLD MIDAS BATCH DATA!!!"
            Close #1
            End
        End If
    End If
  End If
 
  Wend
  Close #1
  sTemp2 = " "
  Open "Y:\Middle Office\AS400\DATA\tabtb10.txt" For Output As #1
  Print #1, sTemp
  Print #1, sTemp2
  Close 1
   
End Sub
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18791973
I don't "think" that'll matter, as

Right(arrDates(1), 7)

would grab the rightmost 7 characters...

You might alter it to

If Trim(Right(arrDates(1), 7)) = Day(Date) & UCase(Left(MonthName(Month(Date)), 3)) & Year(Date) Then
 
to remove any white space for the missing 0 character..
0
 

Author Comment

by:f19l
ID: 18792052
And I have to put all of this code into a vbs file?
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18792057
Yes.
0
 

Author Comment

by:f19l
ID: 18792433
I will start testing this and let you know. In the mean-time I have another question relating to Access called access automation. Perhaps you could have a look at that one, there is someone else having a look but you could fix it faster.
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18792443
<chuckle>...I'm not so sure about that...I'm no where near the expert in VBA coding, but I'll certainly have a looksee...
0
 

Author Comment

by:f19l
ID: 18792734
I think I need to make a slight adjustment. Instead of looking at the second date, which is on the right, the code needs to look at the first date, which is on the left.
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18792848
Did the above work with the date on the right?
Here's the other version...simply use the 0 index of the array

Dim objFSO: Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objShell: Set objShell = CreateObject("Wscript.shell")
strFile="C:\YourSourceFile.txt"
Dim objFile: Set objFile = objFSO.OpenTextFile(strFile)
arrDates = Split(objFile.ReadLine, " ")

If Right(arrDates(0), 7) = Day(Date) & UCase(Left(MonthName(Month(Date)), 3)) & Year(Date) Then
    objFile.Close
    Set objFile = objFSO.CreateTextFile(strFile)
    objFile.WriteLine arrDates(0) & " " & arrDates(1)
    objFile.Close
    Set objFile = Nothing
    objShell.Run "C:\OtherScript.vbs"
    Set objFSO = Nothing
    Set objShell = Nothing
    wscript.quit
Else
    MsgBox "Date is not equal to today's date."
    wscript.quit
End If
0
 

Author Comment

by:f19l
ID: 18793026
I made the changes but it is coming up with the message that the dates do not match. I am copying the latest extract from the text file.
 128692MAR07 1286623MAR07
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18793040
does it have that leading space there?
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:f19l
ID: 18793061
Yes. It will always have the leading space and a gap between the two text strings.
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18793127
So, in this example,
128692MAR07 1286623MAR07
is supposed to mean Mar 2nd of 2007?
cause there's no space now - and 92 will of course, be invalid... :\
0
 

Author Comment

by:f19l
ID: 18793284
Sorry, a typo error on my part. So just to explain again, using March as an example the format is that for any date between the 1st March to the 9th March the information will be displayed as "D/MMM/YY" which means that the 1st March will be shown as 1MAR07, the 2nd March will be shown as 2MAR07 all the way through to the 9th March which will be shown as 9MAR07. The important point to remember is that during these dates there will be a space WITHIN each text string for example:
 12869 3MAR07 12866 2MAR07
From the 10th March onwards the dates are displayed using the format "DD/MMM/YY" with the result that there will be NO GAP WITHIN each text string
 1288526MAR07 1288223MAR07
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18793551
A small difference perhaps...
This assumes that if the space is there (between the first date section), that it'll skip over it, because it's a numeric value (which the version with the month name in it won't be)...


Dim objFSO: Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objShell: Set objShell = CreateObject("Wscript.shell")
strFile = "C:\testing\test.txt"
Dim objFile: Set objFile = objFSO.OpenTextFile(strFile)
arrDates = Split(objFile.ReadLine, " ")

For x = 0 To UBound(arrDates)
    If Trim(arrDates(x)) <> "" Then
      If Not IsNumeric(arrDates(x)) Then
        strDate = arrDates(x)
        Exit For
      End If
    End If
Next

If Trim(Right(strDate, 7)) = Day(Date) & UCase(Left(MonthName(Month(Date)), 3)) & Year(Date) Then
    objFile.Close
    Set objFile = objFSO.CreateTextFile(strFile)
    objFile.WriteLine arrDates(0) & " " & arrDates(1)
    objFile.Close
    Set objFile = Nothing
    objShell.Run "C:\OtherScript.vbs"
    Set objFSO = Nothing
    Set objShell = Nothing
    wscript.quit
Else
    MsgBox "Date is not equal to today's date."
    wscript.quit
End If
0
 

Author Comment

by:f19l
ID: 18793605
It's still coming up with the date error message. Is there anyway for me to step through the code?
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18793647
Not step, but you can display echo messages...

Dim objFSO: Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objShell: Set objShell = CreateObject("Wscript.shell")
strFile = "C:\testing\test.txt"
Dim objFile: Set objFile = objFSO.OpenTextFile(strFile)
arrDates = Split(objFile.ReadLine, " ")

wscript.echo "strDate is " & strDate

For x = 0 To UBound(arrDates)
    If Trim(arrDates(x)) <> "" Then
      If Not IsNumeric(arrDates(x)) Then
        strDate = arrDates(x)
        Exit For
      End If
    End If
Next

If Trim(Right(strDate, 7)) = Day(Date) & UCase(Left(MonthName(Month(Date)), 3)) & Year(Date) Then
    objFile.Close
    Set objFile = objFSO.CreateTextFile(strFile)
    objFile.WriteLine arrDates(0) & " " & arrDates(1)
    objFile.Close
    Set objFile = Nothing
    objShell.Run "C:\OtherScript.vbs"
    Set objFSO = Nothing
    Set objShell = Nothing
    wscript.quit
Else
    MsgBox "Date is not equal to today's date."
    wscript.quit
End If
0
 

Author Comment

by:f19l
ID: 18793688
When I use the code I get a windows script host with the message "strDate is ". The variable strDate seems to be empty. After which the date mismatch message appears.
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18794263
arg..cause I dropped it in the wrong place...

'''remove from herewscript.echo "strDate is " & strDate

For x = 0 To UBound(arrDates)
    If Trim(arrDates(x)) <> "" Then
      If Not IsNumeric(arrDates(x)) Then
        strDate = arrDates(x)
        Exit For
      End If
    End If
Next

'place here:
wscript.echo "strDate is " & strDate
0
 

Author Comment

by:f19l
ID: 18798741
I have made the amendments you suggested as well as a slight amendment of my own to the date line. The code is now able to recognize the date and check it but it falls down at the point when it tries to open the database vbs file, the error message states that the system can not find the file (line 26, char 5).

Dim objFSO: Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objShell: Set objShell = CreateObject("Wscript.shell")
strFile="Y:\Middle Office\AS400\DATA\tabtb10.txt"
Dim objFile: Set objFile = objFSO.OpenTextFile(strFile)
arrDates = Split(objFile.ReadLine, " ")

For x = 0 To UBound(arrDates)
    If Trim(arrDates(x)) <> "" Then
      If Not IsNumeric(arrDates(x)) Then
        strDate = arrDates(x)
        Exit For
      End If
    End If
Next

wscript.echo "strDate is " & strDate
wscript.echo Day(Date) & UCase(Left(MonthName(Month(Date)), 3)) & Right(Year(Date),2)
wscript.echo trim(Day(Date+7))

If Trim(Right(strDate, 7)) = trim(Day(Date)) & UCase(Left(MonthName(Month(Date)), 3)) & Right(Year(Date),2) Then
    objFile.Close
    Set objFile = objFSO.CreateTextFile(strFile)
    objFile.WriteLine arrDates(0) & " " & arrDates(1)
    objFile.Close
    Set objFile = Nothing
    objShell.Run "Y:\Public\Software and info\MidasAutoload.vbs"
    Set objFSO = Nothing
    Set objShell = Nothing
    wscript.quit
Else
    MsgBox "Date is not equal to today's date."
    wscript.quit
End If
0
 
LVL 67

Accepted Solution

by:
sirbounty earned 500 total points
ID: 18799064
Try adjusting that line to
    objShell.Run "wscript " & chr(34) & "Y:\Public\Software and info\MidasAutoload.vbs" & chr(34)
0
 

Author Comment

by:f19l
ID: 18799089
It works! Thanks for your help on this one. I also looked again at the question access automation and that one works as well so I will accept both your soultions.
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18799095
Glad I could assist you. :^)
0
 

Author Comment

by:f19l
ID: 18806258
sirbounty,

I tried running the code this morning but there is a problem; whilst the arrDates(0) correctly displays the first text string in the file the arrDates(1) variable is blank.

Thanks,

f19l
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18807147
arrDates(1) may be blank because of that space between the month..
Can you post the full working version at this point?
0
 

Author Comment

by:f19l
ID: 18807162
Attached is the most recent copy of the text file that I have:

 1287128MAR07 1287027MAR07


Also the full working version of the vbs file is attached below:

Dim objFSO: Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objShell: Set objShell = CreateObject("Wscript.shell")
strFile="Y:\Middle Office\AS400\DATA\tabtb10.txt"
Dim objFile: Set objFile = objFSO.OpenTextFile(strFile)
arrDates = Split(objFile.ReadLine, " ")
wscript.echo arrDates


For x = 0 To UBound(arrDates)
    If Trim(arrDates(x)) <> "" Then
      If Not IsNumeric(arrDates(x)) Then
        strDate = arrDates(x)
        Exit For
      End If
    End If
Next


If Trim(Right(strDate, 7)) = trim(Day(Date)) & UCase(Left(MonthName(Month(Date)), 3)) & Right(Year(Date),2) Then
    objFile.Close
    Set objFile = objFSO.CreateTextFile(strFile)
    objFile.WriteLine arrDates(0) & " " & arrDates(1)
    objFile.Close
    Set objFile = Nothing
    objShell.Run "wscript " & chr(34) & "Y:\Public\Software and info\MidasAutoload.vbs" & chr(34)
    Set objFSO = Nothing
    Set objShell = Nothing
    wscript.quit
Else
    MsgBox "Date is not equal to today's date."
    wscript.quit
End If
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18807183
Ah, I see...
One way to get at it, is:

  objFile.WriteLine arrDates(x) & " " & arrDates(x+1)

But I think that rather clunky...I'll see what I can come up with.
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18807224
This should be a better option - we just read the full line in first, instead of splitting it - we split the variable...

[....skipping...]
Dim objFile: Set objFile = objFSO.OpenTextFile(strFile)
strData = objFile.ReadLine '<<added this line
arrDates = Split(strData, " ")

[....skipping...]

    Set objFile = objFSO.CreateTextFile(strFile)
    objFile.WriteLine strData '<<< changed this line
    objFile.Close  
[....skip to end...]
0
 

Author Comment

by:f19l
ID: 18807289
It's looks to be working now, thanks. I will have to wait until the morning now to properly check the enitre process but I am sure that there will be no problems. If there are I will contact you again, if you don't mind.
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18807303
Not at all - I'd rather you have a working solution! :^)

I'm fairly sure this will work though - before I was trying to rebuild the array to write it back down (dunno why) - this will actually write that first line as it was originally read...the cancelling out of the condition if the dates don't match allow for the file to remain intact otherwise...
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

706 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now