Write Macro in to VBS

[paulk11]

Dear all

I need to insert a macro in to my VB script which will do two things to a File previously created in the script.

Drop (Change) everything in column A in to lower case

Shorten the text displayed in column A to show only the first 3 characters (from the left).

Any help much appreaciated

Kr
Paul

 

[PHV]

a File previously created in the script
Any chance you could post the relevant code so we know which sort of file you're talking about ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[paulk11]

hi there PHV.

Actually i have written 3 scripts, which i need to combine in to one script but i cant figure that out.
(sorry im very new to VBS)
I can tell you all the below scripts individually work but they are 3 individual files currently.

--------------------------------------------------------
Script 1 (quiries Active Directory and writes to (.CSV)
' Prompt for group names and construct the filter.
strCN = ""
strFilter = "(|"
Do
strCN = InputBox("Enter Common Name of a group or Cancel", "Test")
If (strCN <> "") Then
strFilter = strFilter & "(memberOf=cn=" & strCN _
& ",ou=MY-OU,dc=DOMAIN,dc=co,dc=uk)"
End If
Loop While strCN <> ""

strFilter = strFilter & ")"

' Use the WshShell object to run the csvde command.
If strFilter <> "(|)" Then
Set objShell = CreateObject("Wscript.Shell")
strCmd = "%comspec% /c csvde -f e:\get-group\GroupMembers.csv -r """ & strFilter & """"
strCmd = strCmd & " -l sAMAccountName,givenName,userPrincipalName"
strCmd = strCmd & " -s Server.DOMAIN.co.UK"
objShell.Run strCmd
End If

----------------------------------------------------------
Script 2 (Deletes line 1 from GroupMember.csv)
Option Explicit
Dim filePath, oExcel, oSheet, intLastRow, intRow
Const xlUp = -4162

intRow = 1
intLastRow = 1
filePath = "e:\get-group\GroupMember.csv"

Set oExcel = CreateObject("Excel.Application")
'oExcel.Visible = True
oExcel.Workbooks.Open filepath
Set oSheet = oExcel.ActiveWorkbook.Worksheets(1)
oSheet.Rows("" & intRow & ":" & intLastRow & "").Delete xlUp

oExcel.DisplayAlerts = False
oExcel.ActiveWorkbook.SaveAs filePath
oExcel.ActiveWorkbook.Close
oExcel.Quit
set oSheet = Nothing
Set oExcel = Nothing

----------------------------------------------------------
Script 3 (deletes Column A from GroupMember.csv)
Option Explicit
Dim filePath, oExcel, oSheet, intLastRow, intRow
Const xlLeft = -4162

filePath = "e:\get-group\GroupMember.csv"

Set oExcel = CreateObject("Excel.Application")
'oExcel.Visible = True
oExcel.Workbooks.Open filepath
Set oSheet = oExcel.ActiveWorkbook.Worksheets(1)
oSheet.Columns("A")("A").Delete xlLeft

oExcel.DisplayAlerts = False
oExcel.ActiveWorkbook.SaveAs filePath
oExcel.ActiveWorkbook.Close
oExcel.Quit
set oSheet = Nothing
Set oExcel = Nothing

----------------------------------------------------------

Perhaps first you could help me so all 3 action complete within 1 script...

thanks for your help
Paul

 

[PHV]

Like this ?

Option Explicit
Dim strCN, strFilter, objShell, strCmd, filePath
Dim oExcel, oSheet
' Prompt for group names and construct the filter.
strCN = ""
strFilter = "(|"
Do
  strCN = InputBox("Enter Common Name of a group or Cancel", "Test")
  If (strCN <> "") Then
    strFilter = strFilter & "(memberOf=cn=" & strCN _
                & ",ou=MY-OU,dc=DOMAIN,dc=co,dc=uk)"
  End If
Loop While strCN <> ""
strFilter = strFilter & ")"
' Use the WshShell object to run the csvde command.
If strFilter <> "(|)" Then
  filePath = "e:\get-group\GroupMember.csv"
  Set objShell = CreateObject("Wscript.Shell")
  strCmd = "%comspec% /c csvde -f " & filePath & " -r """ & strFilter & """"
  strCmd = strCmd & " -l sAMAccountName,givenName,userPrincipalName"
  strCmd = strCmd & " -s Server.DOMAIN.co.UK"
  objShell.Run strCmd, , True
  'Deletes line 1 from GroupMember.csv
  Const xlUp = -4162
  Const xlLeft = -4131
  Set oExcel = CreateObject("Excel.Application")
  'oExcel.Visible = True
  oExcel.Workbooks.Open filePath
  Set oSheet = oExcel.ActiveWorkbook.Worksheets(1)
  oSheet.Rows(1).Delete xlUp
  'deletes Column A from GroupMember.csv
  oSheet.Columns(1).Delete xlLeft
  ' saves the file
  oExcel.DisplayAlerts = False
  oExcel.ActiveWorkbook.SaveAs filePath
  oExcel.ActiveWorkbook.Close
  oExcel.Quit
  Set oSheet = Nothing
  Set oExcel = Nothing
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[paulk11]

PHV

Your "Bolted" together script works a charm.!!

Now column A looks like as below, so as per original post i need to manipulate this output to change it all to lower case and leave only the 3 characters from the left.

(COL A)

Alicia
Charlie
Maria
Aishling
Tayler

(Needs to become)
ali
cha
mar
ais
tay

Any ideas..?

Paul

 

[PHV]

...
  'deletes Column A from GroupMember.csv
  oSheet.Columns(1).Delete xlLeft[!]
  Dim c
  For Each c In oSheet.Columns(1).Cells
    c.Value = Left(LCase(c), 3)
  Next
[/!]  ' saves the file
  oExcel.DisplayAlerts = False
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[paulk11]

ok that works creates .csv dels column A and row 1
drops A to lower case and sets it to 3 characters.

But when i open the .csv i get

groupmember.csv is already open Reopening will cause any changes you have made to be disgarded Do you want to reopen groupmembers.csv
YES NO


if i do yes i get a runtime error.
if i do no XL loops continually

Paul

 

[PHV]

No ghost excel.exe in the task manager ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[paulk11]

not until i click yes or no,

run it again got a WSH error when i opened the .csv (takes a while to popup).

line 35
character 5
call was rejected by callee
80010001

Paul

 

[PHV]

To discover what happens, uncomment the following line:
'oExcel.Visible = True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[paulk11]

un coment how does one do this..

 

[PHV]

Replace this:
'oExcel.Visible = True
with this:
oExcel.Visible = True

 

[paulk11]

ok done

when i run the script it
opens xl
i see to line deleted
i see the column deleted
i see it change A to Lowercase
i see it change A to 3 chars#

then it just loops

Paul

 

[PHV]

Replace this:
For Each c In oSheet.Columns(1).Cells
with this:
For Each c In oSheet[!].UsedRange[/!].Columns(1).Cells

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[paulk11]

WOW AMAZING UNBELEIVABLE !!!!
Its done !! It Works..!!

thank you so so much PHV you truly are a scholar and a gentleman.

what difference did that last change mean...?
whats the USEDRANGE bit affect..?

drinks are on me (or at least they would be)
Its like real early here and im off to bed, before it all starts again in 4 hours...

cheers
Paul