Convert excel column to an array

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Convert excel column to an array

Logged in as: Guest

arrSession:exec spGetSession 2,2,34625

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Convert excel column to an array
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

Convert excel column to an array - 5/17/2006 1:32:14 PM

dante22879

Posts: 7
Score: 0
Joined: 5/16/2006
Status: offline

This may be a very simple question but I'm very noobish with VBscript and I've spent 2 sleepless days trying to figure out this simple simple problem...yes i'm hopeless with programming but really need your help on this guys..please !

Problem = I have an excel file with one column of numbers as shown below :-

            Column A
Row 1         500
Row 2         501
Row 3         502
Row 4         503
Row 5         504
Row 6         22
Row 7         42
Row 8         100

I want to put all those numbers into a 1-dimensional string array. I don't need to include the row and column names, as long as the number in each row refers to index 0, index 1, index 2 and so forth of the string array.

So the string array should be like this :-

strArray(0) = 500
strArray(1) = 501
.
.
.

strArray(8) = 100

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

This is what I've done so far to extract from the excel file. But just dunno how to put the info into a string array.



Any ideas please ????

I'd really appreciate the help. Thanks !

< Message edited by dante22879 -- 5/17/2006 1:33:15 PM >

Post #: 1

RE: Convert excel column to an array - 5/17/2006 4:55:03 PM

ginolard

Posts: 1082
Score: 21
Joined: 8/10/2005
Status: offline

Here you go

http://www.microsoft.com/technet/scriptcenter/resources/qanda/oct05/hey1012.mspx

_____________________________

Author of ManagePC - http://managepc.net
AD Query Template - http://www.visualbasicscript.com/m_40609/tm.htm
Consolidated Scripting Framework - http://www.visualbasicscript.com/m_59109/tm.htm

(in reply to dante22879)

Post #: 2

RE: Convert excel column to an array - 5/17/2006 6:55:57 PM

dante22879

Posts: 7
Score: 0
Joined: 5/16/2006
Status: offline

hi,

thanks a lot for your help. However it only solves half of my problem. Here's why :-

Excel Spreadsheet :-

            Column A
Row 1         500
Row 2         501
Row 3         502
Row 4         503
Row 5         504
Row 6         22
Row 7         42
Row 8         100



So arrExcelValues are populated as :-

arrExcelValues(0) = 500
arrExcelValues(1) = 501
.
.
.

arrExcelValues(8) = 100

I now have to put the whole array of numbers into the string array IPSecPermitTCPPorts as shown below.

uint32 EnableIPSec(
string IPSecPermitTCPPorts[],
string IPSecPermitUDPPorts[],
string IPSecPermitIPProtocols[]
);

EnableIPSec is a method of the Win32_NetworkAdapterConfiguration Class.

And i'm going to use it something like....

For Each objNicConfig in colNicConfig
intIPSecReturn = objNicConfig.EnableIPSec(IPSecPermitTCPPorts, arrPermittedUDPPorts, arrPermittedIPProtocols)

I assumed it would be quite straightforward to just replace it just like this :-

For Each objNicConfig in colNicConfig
intIPSecReturn = objNicConfig.EnableIPSec(arrExcelValues, arrPermittedUDPPorts, arrPermittedIPProtocols)

But it wont work. Nothing displays.

Please help.

(in reply to ginolard)

Post #: 3

RE: Convert excel column to an array - 5/17/2006 7:55:45 PM

ginolard

Posts: 1082
Score: 21
Joined: 8/10/2005
Status: offline

Try this, the Join command will combine all elements of an array into a single string

For Each objNicConfig in colNicConfig
intIPSecReturn = objNicConfig.EnableIPSec(Join(arrExcelValues) arrPermittedUDPPorts, arrPermittedIPProtocols)
Next

_____________________________

Author of ManagePC - http://managepc.net
AD Query Template - http://www.visualbasicscript.com/m_40609/tm.htm
Consolidated Scripting Framework - http://www.visualbasicscript.com/m_59109/tm.htm

(in reply to dante22879)

Post #: 4

RE: Convert excel column to an array - 5/17/2006 8:40:16 PM

dante22879

Posts: 7
Score: 0
Joined: 5/16/2006
Status: offline

Nope it still wont work. Anyway I'll just paste the whole code......

I just have a gut feeling i'm very close to making it work....grrrr

Basically this code is to add in the list of ports permitted in the windows tcp/ip port filtering, instead of adding one by one manually.

Dim arrExcelValues()

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Documents and Settings\user\Desktop\cuser.xls")
'objExcel.Visible = True

i = 1 'counter to track row in spreadsheet
x = 0 'counter to track position in array

Do Until objExcel.Cells(i, 1).Value = ""
  ReDim Preserve arrExcelValues(x)
  arrExcelValues(x) = objExcel.Cells(i, 1).Value
  i = i + 1
  x = x + 1
Loop

'************************************************************

Const ALLOW_ALL = "0"
Const ALLOW_NONE = ""

strComputer = "."
blnIPFilterSecurityEnabled = "False"
arrPermittedTCPPorts = Join(arrExcelValues) [color="#000000"]'I suspect this line is wrong right ? When I declare it as arrPermittedTCPPorts = Array("500","501"....etc), it works but how do I list those port numbers in the excel file in an array like ("500","501"........"100") ??[/color]
arrPermittedUDPPorts = Array(ALLOW_NONE)
arrPermittedIPProtocols = Array(ALLOW_ALL)

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colNicConfig = objWMIService.ExecQuery _
("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")

If blnIPFilterSecurityEnabled = False Then
 WScript.Echo VbCrLf & "Enabling IP Filtering ..."
 Set objAllNicsConfig = objWMIService.Get("Win32_NetworkAdapterConfiguration")
 intFilterReturn = objAllNicsConfig.EnableIPFilterSec(True)
 If intFilterReturn = 0 Then
  WScript.Echo "  IP Filtering enabled for all network adapters."
  SpecifyFilters
 ElseIf intFilterReturn = 1 Then
  WScript.Echo "  IP Filtering enabled for all network adapters." _
   & VbCrLf & "  Must reboot for changes to take effect."
  SpecifyFilters
 Else
  WScript.Echo "  Unable to enable IP Filtering."
 End If
Else
 WScript.Echo VbCrLf & "IP Filtering already enabled for all network adapters."
 SpecifyFilters
End If

'***********************************************************************

Sub SpecifyFilters

Set colNicConfig = objWMIService.ExecQuery _
("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")
For Each objNicConfig in colNicConfig
 intIPSecReturn = objNicConfig.EnableIPSec(arrPermittedTCPPorts, _
 arrPermittedUDPPorts, arrPermittedIPProtocols)
 If intIPSecReturn = 0 Then
  WScript.Echo "  Filters enabled on " & _
   objNicConfig.Index & " for specified ports and protocols."
 ElseIf intIPSecReturn = 1 Then
  WScript.Echo "  Filters enabled on " & _
   objNicConfig.Index & " for specified ports and protocols." _
   & VbCrLf & "  Must reboot for changes to take effect."
 Else
  WScript.Echo "  Unable to enable filters on " & _
   objNicConfig.Index & " for specified ports and protocols."
 End If
Next

End Sub

< Message edited by dante22879 -- 5/17/2006 8:41:37 PM >

(in reply to ginolard)

Post #: 5

RE: Convert excel column to an array - 5/17/2006 9:23:50 PM

ehvbs

Posts: 2220
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Hi dante22879,

if you look at Microsoft's own sample code, you'll see that they use something like

     arrPermittedTCPPorts = Array("80", "443")

to initialize this variable. Can you test your code with arrPermittedTCPPorts initialized exactly as this?

Looking at your "read from Excel" code, I can think of only one possible problem: If the values
are numbers, your array wouldn't look like Array("80", "443") but Array(80, 443). This could
cause problems. To test that, just replace

   arrExcelValues(x) = objExcel.Cells(i, 1).Value

with

   arrExcelValues(x) = CStr( objExcel.Cells(i, 1).Value )

or even

   arrExcelValues(x) = Trim( CStr( objExcel.Cells(i, 1).Value ) )

Sorry to let you test my wild guesses, but it wouldn't be easy for me to setup
a test environment.

(in reply to dante22879)

Post #: 6

RE: Convert excel column to an array - 5/18/2006 12:08:58 PM

dante22879

Posts: 7
Score: 0
Joined: 5/16/2006
Status: offline

Yes I know microsoft initializes the string array as :-

arrPermittedTCPPorts = Array("80", "443")

And you're probably right about the excel numeric extraction ie. it comes out as arrPermittedTCPPorts = Array(80,443.......etc)

But I've tried your code but it still comes out as type mismatch error at this line :-

intIPSecReturn = objNicConfig.EnableIPSec(Join(arrExcelValues), arrPermittedUDPPorts, arrPermittedIPProtocols)

Here's the code I've modified again but still wont' work......

Dim arrExcelValues()

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Documents and Settings\user\Desktop\cuser.xls")
'objExcel.Visible = True

i = 1 'counter to track row in spreadsheet
x = 0 'counter to track position in array

Do Until objExcel.Cells(i, 1).Value = ""
   ReDim Preserve arrExcelValues(x)
   arrExcelValues(x) = CStr(objExcel.Cells(i, 1).Value)
   i = i + 1
   x = x + 1
Loop

'************************************************************

Const ALLOW_ALL = "0"
Const ALLOW_NONE = ""

strComputer = "."
blnIPFilterSecurityEnabled = "False"
arrPermittedTCPPorts = Join(arrExcelValues)
arrPermittedUDPPorts = Array(ALLOW_NONE)
arrPermittedIPProtocols = Array(ALLOW_ALL)

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colNicConfig = objWMIService.ExecQuery _
("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")

If blnIPFilterSecurityEnabled = False Then
 WScript.Echo VbCrLf & "Enabling IP Filtering ..."
 Set objAllNicsConfig = objWMIService.Get("Win32_NetworkAdapterConfiguration")
 intFilterReturn = objAllNicsConfig.EnableIPFilterSec(True)
 If intFilterReturn = 0 Then
   WScript.Echo "  IP Filtering enabled for all network adapters."
   SpecifyFilters
 ElseIf intFilterReturn = 1 Then
   WScript.Echo "  IP Filtering enabled for all network adapters." _
    & VbCrLf & "  Must reboot for changes to take effect."
   SpecifyFilters
 Else
   WScript.Echo "  Unable to enable IP Filtering."
 End If
Else
 WScript.Echo VbCrLf & "IP Filtering already enabled for all network adapters."
 SpecifyFilters
End If

'***********************************************************************

Sub SpecifyFilters

Set colNicConfig = objWMIService.ExecQuery _
("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")
For Each objNicConfig in colNicConfig
 intIPSecReturn = objNicConfig.EnableIPSec(arrPermittedTCPPorts, _
  arrPermittedUDPPorts, arrPermittedIPProtocols)
 If intIPSecReturn = 0 Then
   WScript.Echo "  Filters enabled on " & _
    objNicConfig.Index & " for specified ports and protocols."
 ElseIf intIPSecReturn = 1 Then
   WScript.Echo "  Filters enabled on " & _
    objNicConfig.Index & " for specified ports and protocols." _
    & VbCrLf & "  Must reboot for changes to take effect."
 Else
   WScript.Echo "  Unable to enable filters on " & _
    objNicConfig.Index & " for specified ports and protocols."
 End If
Next

End Sub

Please someone help me...i'm almost at my wit's end. :-(

I have 16,000 over ports to add in manually for the tcp/ip filtering process on 20 over servers....so you can imagine how important it is for me to get a script working....

(in reply to ehvbs)

Post #: 7

RE: Convert excel column to an array - 5/18/2006 1:24:59 PM

dalemontgomery45177

Posts: 26
Score: 0
Joined: 5/11/2006
Status: offline

Forgive me if I'm way off base. But couldn't this be made simpler by saving the Excel file as a CSV first (you would need to remove all information besides the list of ports starting at A1)? Then read the entire file into an array and then use it to make the string needed? You may want to make a separate csv for each protocol type or limit the reading of the array based on row number

Note: untested so you may find a mistake or two

(in reply to dante22879)

Post #: 8

RE: Convert excel column to an array - 5/18/2006 2:52:47 PM

dante22879

Posts: 7
Score: 0
Joined: 5/16/2006
Status: offline

ok thanks mate....

i'm very close to cracking it now....... just a matter of string manipulation.

When I run it, it comes up with this result......

Array("1","2","3","4","5","10","",)

My csv file has something very basic like this :-

1
2
3
4
5
10

Starting from A1 (column 1, row 1)....

How do I eliminate the "", part at the end in the above array ? Is it something to do with an empty row/column after '10' in the csv file ?

Pls help anyone....so close now...

(in reply to dante22879)

Post #: 9

RE: Convert excel column to an array - 5/18/2006 5:22:46 PM

ehvbs

Posts: 2220
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Hi dante22879,

you used arrExcelValues like this:

   intIPSecReturn = objNicConfig.EnableIPSec(Join(arrExcelValues), arrPermittedUDPPorts, arrPermittedIPProtocols)

that should be

   intIPSecReturn = objNicConfig.EnableIPSec( arrExcelValues, arrPermittedUDPPorts, arrPermittedIPProtocols)

cf

   uint32 EnableIPSec(
   string IPSecPermitTCPPorts[],
string IPSecPermitUDPPorts[],
string IPSecPermitIPProtocols[]
);

I'd stick to the Excel way, but if you want to work with the .csv the fastest way is to remove the
last vbCrLf manually in the file, read and split the list into myArray
    myArray = Split(strText,vbcrlf)
and use myArray like this:
   intIPSecReturn = objNicConfig.EnableIPSec( myArray, arrPermittedUDPPorts, arrPermittedIPProtocols)
Don't convert it into a string again.

(in reply to dante22879)

Post #: 10

RE: Convert excel column to an array - 5/18/2006 6:59:54 PM

dante22879

Posts: 7
Score: 0
Joined: 5/16/2006
Status: offline

Ok cheers guys !

I've managed to crack it......Nothing special as most ppl perform port filtering at the firewall side....but oh well, at least I've learnt something from you all ! Once again, thanks a lot !

Here's the code if anyone needs it (using .csv file to generate range of port numbers) :-

ConfirmDelete = MsgBox ("Are you sure you want to continue with TCP/IP Port Filtering ?", _
   VbYesNo OR VBDefaultButton1, "TCP/IP Port Filtering")
If ConfirmDelete = VbNo then
   Wscript.Quit
End If

Const ForReading = 1

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Documents and Settings\user\Desktop\TCPPorts.csv", ForReading)

strText = objFile.ReadAll
'Split based on Carriage return/Line Feed

myArray = Split(strText,vbcrlf)

objFile.Close

arrPermittedTCPPorts = " ("
for i = 0 to Ubound(myArray)  
 If myArray(i) <> "" Then
    arrPermittedTCPPorts = arrPermittedTCPPorts & myArray(i)
    arrPermittedTCPPorts = arrPermittedTCPPorts & ","
    arrPermittedTCPPorts1 = arrPermittedTCPPorts & ")"
 End If
Next

WScript.Echo "Permitted Only TCP Ports" & vbcrlf & Replace(arrPermittedTCPPorts1, ",)" , ")")

'************************************************************

Const ALLOW_ALL = "0"
Const ALLOW_NONE = ""

strComputer = "."
blnIPFilterSecurityEnabled = "False"
'arrPermittedTCPPorts = Join(arrExcelValues)
arrPermittedUDPPorts = Array(ALLOW_ALL)
arrPermittedIPProtocols = Array(ALLOW_ALL)

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colNicConfig = objWMIService.ExecQuery _
("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")

If blnIPFilterSecurityEnabled = False Then
 WScript.Echo VbCrLf & "Enabling IP Filtering ..."
 blnIPFilterSecurityEnabled = objNicConfig.IPFilterSecurityEnabled
 Set objAllNicsConfig = objWMIService.Get("Win32_NetworkAdapterConfiguration")
 intFilterReturn = objAllNicsConfig.EnableIPFilterSec(True)
 If intFilterReturn = 0 Then
   WScript.Echo "  IP Filtering enabled for all network adapters."
   SpecifyFilters
 ElseIf intFilterReturn = 1 Then
   WScript.Echo "  IP Filtering enabled for all network adapters." _
    & VbCrLf & "  Must reboot for changes to take effect."
   SpecifyFilters
 Else
   WScript.Echo "  Unable to enable IP Filtering."
 End If
Else
 WScript.Echo VbCrLf & "IP Filtering already enabled for all network adapters."
 SpecifyFilters
End If

'***********************************************************************

Sub SpecifyFilters

Set colNicConfig = objWMIService.ExecQuery _
("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")
For Each objNicConfig in colNicConfig
 intIPSecReturn = objNicConfig.EnableIPSec(myArray, _
  arrPermittedUDPPorts, arrPermittedIPProtocols)
 If intIPSecReturn = 0 Then
   WScript.Echo "  Filters enabled on " & "NIC" & _
    objNicConfig.Index & " for specified ports and protocols."
 ElseIf intIPSecReturn = 1 Then
   WScript.Echo "  Filters enabled on " & "NIC" & _
    objNicConfig.Index & " for specified ports and protocols." _
    & VbCrLf & "  Must reboot for changes to take effect."
 Else
   WScript.Echo "  Unable to enable filters on " & _
    objNicConfig.Index & " for specified ports and protocols."
 End If
Next

End Sub

(in reply to ehvbs)