VB to search Excell Sheets

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: wssparky (ws_at_sparky.com)
Date: 02/21/04 

Date: Sat, 21 Feb 2004 09:05:45 -0800

Hi,
 I need some help.
The following code is part of a Time Clock program.
The employees will enter the last 4 numbers of the SSN (Readout)
and the program will open the Excel Book “TimeSheet.xls”
and match it up to the number in cell “E5”.
Each sheet in the book is named as the last 4 numbers of the SSN.
It will then print the date and time in the Excel sheet as well as
display
the employees name “B2”, number ”E5”, and time clocked in or out
on the VB display (Readout2).

The problem is I cant get it to loop through the sheets and find the SSN
.
This is just my approach to the program,
( learned from Devdex.com – Thanks for your Help !!) if you see a better
way
of doing anything else, PLEASE let me know. I need this to run as fast
as possible
and be easy to run as well.

One more thing, I’m just learning VB6 so please go slow.

Thanks for any help in advance

Option Explicit
Dim m_XLApp As Excel.Application
Dim m_XLWorkbook As Excel.Workbook

Private Sub Enter_Click()
   
    Dim FileNamePath As String
    Dim FileName As String
    Dim S1 As Excel.Worksheet
    
    Dim EmpNumber As String
    Dim EmpName As String
    Dim EName As String
    Dim SheetName As String
   
    FileName = "TimeSheet.xls"
    FileNamePath = "C:\TestTime\TimeSheet.xls"

'Clear any info in boxes
    EmpNumber = ""
    EmpName = ""
    
    
    Set m_XLApp = Excel.Application
    m_XLApp.Visible = True
    Set m_XLWorkbook = m_XLApp.Workbooks.Open(FileNamePath)
    m_XLWorkbook.RunAutoMacros Which:=xlAutoOpen
    
  
   For Each S1 In m_XLWorkbook.Worksheets()
        
            EmpNumber = S1.Range("E4")
            EmpName = S1.Range("B2")
        

    If EmpNumber = Readout Then EName = EmpName
    
   
    
' New Employee
    If Readout = "9" Then Module1.NewEmp
    If Readout = "9" Then Readout2 = ""
    If Readout = "9" Then Readout = ""
    If Readout = "" Then Exit Sub
   
   
    
'If Error
    
    If EmpName = "" Then Module1.NoFile
    If EmpName = "" Then Readout = ""
    If EmpName = "" Then Exit Sub
    
    
    Next S1
  

   
    
    
   SheetName = Readout
    m_XLWorkbook.Worksheets(SheetName).Range("A10") = Format(Now, "Short
Date")
    m_XLWorkbook.Worksheets(SheetName).Range("F11") = Label1.Caption
      
    
    Readout2 = Format("Employee Name : " + EmpName) & vbCrLf & _
            Format("Employee Number : " + Readout) & vbCrLf & _
            Format("Time : " + Label1.Caption)
    Readout = ""
    
    m_XLWorkbook.Save
    m_XLWorkbook.Close True
    

End Sub

wssparky______________________
 
We learn by doing, and doing ……and doing ……

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: Extract SSNs from a free form text field...
    ... >These are a few of the ways the SSN could appear in the field. ... string of nine digits after an "SS". ... Ensure your project is highlighted in the project explorer window, then, from ... Dim str As String ...
    (microsoft.public.excel.misc)
  • VBA: Pull Record Into UserForm
    ... I have a worksheet full of Customer Information. ... I want to pull the customers LName, FName, SSN, and info into my form ... Dim LName As String ...
    (microsoft.public.excel.programming)
  • Code to add and verify existing data
    ... I have a form named MASTER based on a table also ... A control of my form in names SSN which stores the client SSN. ... Dim strCode As String ... Dim strFilter As String ...
    (comp.databases.ms-access)
  • Re: Finding Text String Within A String With A Formula
    ... Function SSNAs String ... Dim Character As String ... > SSN-XXXXXXXXX ... > SSN - XXX-XX-XXXX ...
    (microsoft.public.excel.misc)
  • Search pattern
    ... Dim strfile As String ... Dim bAddressFound As Boolean ... Dim strCurrentChar As String ...
    (comp.databases.ms-access)