NEWBIE Question on Excel/VBA 
Author Message
 NEWBIE Question on Excel/VBA

I have an Excel file which contains a list of files and their associated
owners. The owners are identified by their NT logon and I require this data
to either be replaced with their full (real) name or the full names added as
an extra column (alongside the NT Logon info).

I've created an Excel file which contains all the NT logons and their
associated full names from our NT domain so I was thinking of using this as
a lookup table to then populate my original Excel file.

I'm fairly familiar with VBScript (I've used it in some ASPs) but have never
had to do anything with any of the Office applications.

I'd greatly appreciate some guidance (and example code if poss).

I hope this makes sense.

Thanks,
Niall



Fri, 04 Mar 2005 02:12:05 GMT  
 NEWBIE Question on Excel/VBA
Working with Office Applications in VB Script is fairly easy once you
understand what is expected.  I have included some samples for you.
Hope this helps.

Set objExcel = CreateObject("Excel.Application")

With objExcel
        .Application.Visible = False
        .Application.DisplayAlerts = False
        .Application.EnableEvents = False
        .Application.CommandBars("Worksheet Menu Bar").Enabled = False
        .Application.DisplayFormulaBar = False
        .Application.DisplayStatusBar = False
        On Error Resume Next
        For A = 1 To .Application.CommandBars.Count
            .Application.CommandBars(A).Visible = False
        Next

        .Workbooks.Open(strCurrExcelFile)

        indexRow = 1
        indexColumn = 1

        Do While LCase(Trim(.ActiveSheet.Cells(indexRow, 1))) <> "value1"
            indexRow = indexRow + 1
        Loop

        indexRow = indexRow + 1

        intField1 = CInt(Trim(.ActiveSheet.Cells(indexRow, 1)))
        strField2 = CStr(Trim(.ActiveSheet.Cells(indexRow, 2)))

        Do While LCase(Trim(.ActiveSheet.Cells(indexRow, 1))) <>
"value2"
                indexRow = indexRow + 1
        Loop
        indexRow = indexRow + 2

        intItemCount = 0
        Do While intItemCount < intRecordCount

           intItemCount = intItemCount + 1
           intField4 = CInt(Trim(.ActiveSheet.Cells(indexRow, 1)))
           If Len(Trim(.ActiveSheet.Cells(indexRow, 2))) > 15 Then
              strField3 = CStr(Left(Trim(.ActiveSheet.Cells(indexRow,
2)), 15))
           Else  
              strField3 = CStr(Trim(.ActiveSheet.Cells(indexRow, 2)))
           End If

         Loop

         .Workbooks.Close

        End With

objExcel.Application.Quit

You can get information about using office applications at:

http://www.microsoft.com/officedev/articles/Opg/toc/PGTOC.htm

Edward

Quote:

> I have an Excel file which contains a list of files and their associated
> owners. The owners are identified by their NT logon and I require this data
> to either be replaced with their full (real) name or the full names added as
> an extra column (alongside the NT Logon info).

> I've created an Excel file which contains all the NT logons and their
> associated full names from our NT domain so I was thinking of using this as
> a lookup table to then populate my original Excel file.

> I'm fairly familiar with VBScript (I've used it in some ASPs) but have never
> had to do anything with any of the Office applications.

> I'd greatly appreciate some guidance (and example code if poss).

> I hope this makes sense.

> Thanks,
> Niall



Sat, 05 Mar 2005 02:39:21 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. VBS vs. VBA (Newbie question)

2. Windows EXCEL VBA / MAC EXCEL VBA compatibility issues

3. Excel 97 VBA vs Excel 2000 VBA

4. Newbie has problem with VBA User Forms in Excel

5. Access97 VBA newbie question

6. vba Newbie Question

7. Newbie question... add-ins and VBA

8. Newbie VBA question

9. newbie question - word vba

10. Newbie Access97 VBA question

11. Please assist a VB newbie with an easy Excel question

12. Newbie question using Excel

 

 
Powered by phpBB® Forum Software