PDA

View Full Version : first read from A1 then from B1 and so on



saban
09-14-2008, 03:05 AM
Guys I need help. I have xls file which has computer name in column A and office number in column B. Now I can read computer name and store value to variable str computer how can I simultaniousely read the value in B cell (office nr.) and store it into variable

Set objExcel = CreateObject("Excel.Application")
'Gets the directory where our script is running from
'Open our XLS file
Set Workbook = objExcel.Workbooks.Open("C:\read registry\Machines.xls")
'We want to skip the header row, and then the blank row below
loopCount = 1
Do While Not IsEmpty(objExcel.Cells(loopCount, 1).Value)
Dim i 'For looping through the columns on each row
Dim strComputer
For i = 1 To 1
strComputer = objExcel.Cells(loopCount, i).Value
loopCount = loopCount + 1
Next
Loop

Bob Phillips
09-14-2008, 03:16 AM
Set objExcel = CreateObject("Excel.Application")
Dim i 'For looping through the columns on each row
Dim strComputer As String
Dim strOffice As String
'Gets the directory where our script is running from
'Open our XLS file
Set Workbook = objExcel.Workbooks.Open("C:\read registry\Machines.xls")
'We want to skip the header row, and then the blank row below
loopCount = 1
Do While Not IsEmpty(objExcel.Cells(loopCount, 1).Value)
strComputer = objExcel.Cells(loopCount, "A").Value
strOffice = objExcel.Cells(loopCount, "B").Value
loopCount = loopCount + 1
Loop

saban
09-14-2008, 03:54 AM
thnx man I'll try it now, and will let you know

saban
09-14-2008, 04:32 AM
It works perfectly, and I have another question

I put value from registry into variable and it looks like this : MICKA, MICKOVA

How do I separate first and last name or replace comma with tab character so when I write this value to cell the first and last name will be in separate cells in C and D not just in C

Bob Phillips
09-14-2008, 05:39 AM
Dim sName As String
Dim aryNames As Variant

sName = "MICKA, MICKOVA"
aryNames = Split(Replace(sName, " ", ""), ",")
Range("H1:H2") = Application.Transpose(aryNames)
Range("M1:N1") = aryNames

saban
09-14-2008, 07:47 AM
Dim sName As String
Dim aryNames As Variant
'myString = "Micka, mickova"
sName = myString
aryNames = Split(Replace(sName, " ", ""), ",")
Range("H1:H2") = Application.Transpose(aryNames) -waht does this do ????
Range("M1:N1") = aryNames - what does this do ????


I have a problem getting also first name then I would like to have first and last name

like this:
mystring1 = Micka
Mystring2 = Mickova

Are those lines necesary


Range("H1:H2") = Application.Transpose(aryNames) -waht does this do ????
Range("M1:N1") = aryNames - what does this do ????

Any ideas
Thnx

Bob Phillips
09-14-2008, 08:57 AM
You said you wanted to put them in cells, I showed you how.

saban
09-14-2008, 03:01 PM
Yes man I appreciate that very much

but how would i get from :
mystring = "MICKA,, Mickova"
mystring(0) = "MICKA"
mystring(1) = "Mickova"

Thnx man I really apprreciate your help

Bob Phillips
09-14-2008, 03:42 PM
That is exactly what this bit does



Dim sName As String
Dim aryNames As Variant
sName = "MICKA, MICKOVA"
aryNames = Split(Replace(sName, " ", ""), ",")

saban
09-14-2008, 04:04 PM
but aryNames contains only MICKA which variable holds MICKOVA
apart from that it works great