×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

VB code to run Excel dde

VB code to run Excel dde

VB code to run Excel dde

(OP)
I have an Excel spreadsheet setup to record a machines downtime, faults,,,etc. What I would like to do now is have that Excel dde app. start and close automatically at the end of each shift. Is there anyone with a similar VB6 app that would be willing to share? I am an old PLC controls guy that is a newbie to VB. Any comments will be appreciated.

RE: VB code to run Excel dde

Sounds like you are looking for a sheet for each shift. If this is true that’s a lot of sheet. I would like to recommend using database programs instead of excel. Have you investigated this as an option? It seams to be easier and all your excel sheets would import. And the data would be much more manageable.  

RE: VB code to run Excel dde

I did one that automatically closed the sheet and saved it as that day's date, then opened another sheet at MIDNIGHT.

Download the whole thing here:
http://www.mrplc.com/dl/index.php?action=view&view=entry&entryid=74


Here is the CODE:

Sub ChkTime()
Dim strFileName As String
Dim strNowDate As String
Dim lngMonth As Long
Dim lngDay As Long
Dim lngYear As Long
Dim strCharMonth1 As String
Dim strCharMonth2 As String
Dim strCharDay1 As String
Dim strCharDay2 As String
Dim strCharYear As String
Dim lngFileDateMonth As Long
Dim lngFileDateDay As Long
Dim lngFileDateYear As Long
Dim intCountMonth As Integer
Dim intCountDay As Integer
Dim intLoop1 As Integer
Dim intLoop2 As Integer
Dim intLoop3 As Integer
Dim strCurrentOpenedWorkBook As String
strCurrentOpenedWorkBook = ActiveWorkbook.Name

'extracts today's date as serial number
strNowDate = Now()
'converts serial number back to months
lngMonth = Month(strNowDate)
'converts serial number back to days
lngDay = Day(strNowDate)
'converts serial number back to years
lngYear = Year(strNowDate)

'assign a file name from date
strFileName = "M1138-" & lngMonth & "-" & lngDay & "-" & lngYear

    'don't check time if the file is not named by date format
    If strCurrentOpenedWorkBook <> "M1138.xls" Then
    
       'parse the existing file to current date (HEY What can I say! I liked my PARSER code!)
        For intLoop1 = 1 To Len(strCurrentOpenedWorkBook)
            If Mid$(strCurrentOpenedWorkBook, intLoop1, 1) = "-" Then
                    strCharMonth1 = Mid$(strCurrentOpenedWorkBook, intLoop1 + 1, 1)
                    strCharMonth2 = Mid$(strCurrentOpenedWorkBook, intLoop1 + 2, 1)
                    intLoop1 = intLoop1 + 1
                    Exit For
            End If
            
            Next
            
            For intLoop2 = intLoop1 To Len(strCurrentOpenedWorkBook)
                If Mid$(strCurrentOpenedWorkBook, intLoop2, 1) = "-" Then
                    strCharDay1 = Mid$(strCurrentOpenedWorkBook, intLoop2 + 1, 1)
                    strCharDay2 = Mid$(strCurrentOpenedWorkBook, intLoop2 + 2, 1)
                    intLoop2 = intLoop2 + 1
                    Exit For
                End If
            Next
            
            For intLoop3 = intLoop2 To Len(strCurrentOpenedWorkBook)
                If Mid$(strCurrentOpenedWorkBook, intLoop3, 1) = "-" Then
                    strCharYear = Mid$(strCurrentOpenedWorkBook, intLoop3 + 1, 4)
                    Exit For
                End If
            Next
            
            If strCharMonth2 = "-" Then
                lngFileDateMonth = strCharMonth1
            Else
                lngFileDateMonth = (strCharMonth1 * 10) + strCharMonth2
            End If
        
            If strCharDay2 = "-" Then
                lngFileDateDay = strCharDay1
            Else
                lngFileDateDay = (strCharDay1 * 10) + strCharDay2
            End If
        
            lngFileDateYear = strCharYear
        
        'compare existing file to today's date
        If lngFileDateYear < lngYear Or lngFileDateMonth < lngMonth Or _
        lngFileDateDay < lngDay Then
            
            'save file before close
            ActiveWorkbook.Save
            
                    
            'select all data rows
            Rows("3:65500").Select
            
            'clear all data
            Selection.ClearContents
            
            'select a cell to get ready
            Range("A3").Select
            
            Range("INDATA!A3").Value = 3

            'save a new day under new file name
            ActiveWorkbook.SaveAs Filename:="C:\qsi\" & strFileName, FileFormat:= _
            xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False
            Application.Run Macro:="Auto"
        Else
            GoTo DateOk
        End If
    
    End If
DateOk:
End Sub

Chris Elston
Automation & Controls Engineer
http://www.mrplc.com
Download Sample PLC Ladder Logic Code
at MrPLC.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources