-
February 20th, 2011, 09:09 AM
#1
looping problem VB
Hi,
I have tried to create VB. but i can't get it to work could someone help me with this?
I have created the one below but think there might be to many lines maybe around 1000 lines like that. hoping someone could help me.
Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet
Dim xlRow As Integer
Dim x1Column As Integer
Dim CTY As Integer
Dim TEXT As String
Dim PRICE As String
Dim ACCOUNT As String
Dim NO_OF As String
Dim Count
xlRow = 1
Count = 1
'If Not objWorksheet.Range("A" & xlRow).Value Like "678*" Then
'xlRow = xlRow + 1
'If Not objWorksheet.Range("A" & xlRow).Value Like "678*" Then
'xlRow = xlRow + 1
'If Not objWorksheet.Range("A" & xlRow).Value Like "678*" Then
'xlRow = xlRow + 1
'If Not objWorksheet.Range("A" & xlRow).Value Like "678*" Then
'xlRow = xlRow + 1
'If Not objWorksheet.Range("A" & xlRow).Value Like "678*" Then
'xlRow = xlRow + 1
'If Not objWorksheet.Range("A" & xlRow).Value Like "678*" Then
'xlRow = xlRow + 1
'If Not objWorksheet.Range("A" & xlRow).Value Like "678*" Then
'xlRow = xlRow + 1
'If Not objWorksheet.Range("A" & xlRow).Value Like "678*" Then
'xlRow = xlRow + 1
'If Not objWorksheet.Range("A" & xlRow).Value Like "678*" Then
'xlRow = xlRow + 1
'If Not objWorksheet.Range("A" & xlRow).Value Like "678*" Then
'xlRow = xlRow + 1
-
February 20th, 2011, 11:54 AM
#2
Re: looping problem VB
Well you did not tell us what you are really trying to do nor what problem you are having but you do not have any loop in your code so I would assume that is why there is a problem with looping or rather not looping.
Code:
For x=1 to 10
'do something
Next
Always use [code][/code] tags when posting code.
-
February 20th, 2011, 01:52 PM
#3
Re: looping problem VB
Sorry for not being more specific i wanted to create a loop but all my attempts ended up with me creating a lot of linies instead.
I have something from an excel sheet into a AS400.
xlRow is the row i want to find something in the excel sheet i start in in row 1
Dim xlRow As Integer
Dim Count
dim Dim objWorksheet As Excel.Worksheet
xlRow = 1
Count = 1
' I want to look in the excel sheet per row until i get to that specific row containing a let's say i begins with 678
If Not objWorksheet.Range("A" & xlRow).Value Like "678*" Then
xlRow = xlRow + 1
'then i go to the next line xlRow = xlRow + 1 I want to have a loop on above with 500 linies but does work with the belows:
Do While xlRow < 500
If Not objWorksheet.Range("A" & xlRow).Value Like "678*" Then
xlRow = xlRow + 1
End If
xlRow = xlRow + 1
Loop
-
February 20th, 2011, 02:34 PM
#4
Re: looping problem VB
Use a SQL connection, instead
-
February 20th, 2011, 09:20 PM
#5
Re: looping problem VB
Not sure what you mean by does not work? If the lines worked then that should work in a loop as well as it really is no different.
The only thing I see your code doing in the loop is skipping a row when the IF test is true, nothing is being done with the data so I have no idea what you are trying to do nor what problem you are experiencing.
The lines in the first post do not do anything either. They are all commented out and if the comments are removed would trigger errors as you have block IF without matching End IF
Always use [code][/code] tags when posting code.
-
February 21st, 2011, 03:12 AM
#6
Re: looping problem VB
Below is the VB the makro i have created with didn't work. what I did is instead a a loop have created 100 linies with looks like this:
If Not objWorksheet.Range("A" & xlRow).Value Like "678*" Then
xlRow = xlRow + 1
This is the only way i can make it find the data i want. the loop does not find any data just close the makro without putting any thing in.
'----------------------------------------------------------------
Sub tese()
With Session
Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet
Dim strExcelFileName As String
strExcelFileName = "C:\test.xlsx"
Set objExcel = New Excel.Application
objExcel.Visible = False
objExcel.UseSystemSeparators = False
Set objWorkbook = objExcel.Workbooks.Open(strExcelFileName)
Set objWorksheet = objWorkbook.Sheets("Pivot")
Dim xlRow As Integer
Dim x1Column As Integer
Dim CTY As Integer
Dim TEXT As String
Dim PRICE As String
Dim ACCOUNT As String
Dim NO_OF As String
Dim Count
CTY = InputBox("Country?")
If CTY = 678 Then
xlRow = 1
Count = 1
Do While xlRow < 500
If Not objWorksheet.Range("A" & xlRow).Value Like "678*" Then
xlRow = xlRow + 1
End If
xlRow = xlRow + 1
Loop
Do While objWorksheet.Range("A" & xlRow).Value Like "678*"
TEXT = objWorksheet.Range("A" & xlRow).TEXT
PRICE = objWorksheet.Range("B" & xlRow).TEXT
ACCOUNT = objWorksheet.Range("C" & xlRow).Value
NO_OF = objWorksheet.Range("D" & xlRow).Value
If Count = 1 Then
MoveCursor 19, 23
.TransmitTerminalKey rcIBMEraseEOFKey
.TransmitANSI "1"
.MoveCursor 20, 25
.TransmitTerminalKey rcIBMEraseEOFKey
.TransmitANSI TEXT
.MoveCursor 19, 40
.TransmitTerminalKey rcIBMEraseEOFKey
.TransmitANSI PRICE
.MoveCursor 19, 51
.TransmitTerminalKey rcIBMEraseEOFKey
.TransmitANSI ACCOUNT
.MoveCursor 19, 25
.TransmitTerminalKey rcIBMEraseEOFKey
.TransmitANSI NO_OF
.MoveCursor 19, 65
.TransmitTerminalKey rcIBMEraseEOFKey
.TransmitANSI "EUR"
.MoveCursor 19, 71
.TransmitTerminalKey rcIBMEraseEOFKey
.TransmitANSI "907.30"
.MoveCursor 19, 58
.TransmitTerminalKey rcIBMEraseEOFKey
.TransmitANSI "1"
'only in test
.MoveCursor 19, 62
.TransmitTerminalKey rcIBMEraseEOFKey
.TransmitANSI "1"
'end only in test
.TransmitTerminalKey rcIBMEnterKey
.WaitForEvent rcKbdEnabled, "15", "1", 1, 1
.WaitForEvent rcEnterPos, "15", "0", 19, 7
End If
If Count = 2 Then
MoveCursor 19, 23
.TransmitTerminalKey rcIBMEraseEOFKey
.TransmitANSI "1"
.MoveCursor 20, 25
.TransmitTerminalKey rcIBMEraseEOFKey
.TransmitANSI TEXT
.MoveCursor 19, 40
.TransmitTerminalKey rcIBMEraseEOFKey
.TransmitANSI PRICE
.MoveCursor 19, 51
.TransmitTerminalKey rcIBMEraseEOFKey
.TransmitANSI ACCOUNT
.MoveCursor 19, 25
.TransmitTerminalKey rcIBMEraseEOFKey
.TransmitANSI NO_OF
.MoveCursor 19, 65
.TransmitTerminalKey rcIBMEraseEOFKey
.TransmitANSI "EUR"
.MoveCursor 19, 71
.TransmitTerminalKey rcIBMEraseEOFKey
.TransmitANSI "907.30"
.MoveCursor 19, 58
.TransmitTerminalKey rcIBMEraseEOFKey
.TransmitANSI "1"
'only in test
.MoveCursor 19, 62
.TransmitTerminalKey rcIBMEraseEOFKey
.TransmitANSI "1"
'end only in test
.TransmitTerminalKey rcIBMEnterKey
.WaitForEvent rcKbdEnabled, "15", "1", 1, 1
.WaitForEvent rcEnterPos, "15", "0", 19, 7
Count = 1
End If
Count = Count + 1
xlRow = xlRow + 1
Loop
.TransmitTerminalKey rcIBMEnterKey
.WaitForEvent rcKbdEnabled, "30", "1", 1, 1
.WaitForEvent rcEnterPos, "30", "0", 19, 7
End If
NoMoreLines:
'----------------------------------------
objExcel.UseSystemSeparators = True
objWorkbook.Save
objExcel.Quit
Set objExcel = Nothing
Set objWorkbook = Nothing
Set objWorksheet = Nothing
End With
End Sub
'----------------------------------------------------------------
-
February 21st, 2011, 03:14 AM
#7
Re: looping problem VB
Originally Posted by dglienna
Use a SQL connection, instead
Any how do I do that instead?
-
February 21st, 2011, 09:01 AM
#8
Re: looping problem VB
You need to set a reference to the Ms Excel 12.0 library
Code:
Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet
Dim strExcelFileName As String
Private Sub Command1_Click()
strExcelFileName = "C:\test.xlsx"
Set objExcel = New Excel.Application
objExcel.Visible = False
Set objWorkbook = objExcel.Workbooks.Open(strExcelFileName)
Set objWorksheet = objWorkbook.Sheets("Sheet1")' Chage this to "pivot1"
For n = 1 To 500
If objWorksheet.Range("A" & n).Value = "678" Then
MsgBox "Line " & n & " is the value you are looking for"
Exit For
End If
Next n
End Sub
-
February 21st, 2011, 09:01 AM
#9
Re: looping problem VB
Code:
Do While xlRow < 500
If Not objWorksheet.Range("A" & xlRow).Value Like "678*" Then
xlRow = xlRow + 1
End If
xlRow = xlRow + 1
Loop
Your loop is not doing anything other than setting xlRow to 500 0r 501
You have to place the code to do what you want to do within the loop if you expect something to be done.
Also as coded your loop will skip a line everytime the If Statement is true as it will add in the If statement and add after the If statement so when it is true it actually adds 2 and when false it will add 1 but that is the only thing it is doing.
If your second loop xlRow has a starting value of 500 or 501 and you are incrementing that value again.
I do not normally program in Excel so I am not sure about the range statements or a couple of others you have there but that loop structure is way off.
Last edited by DataMiser; February 21st, 2011 at 09:05 AM.
Always use [code][/code] tags when posting code.
-
February 21st, 2011, 01:26 PM
#10
Re: looping problem VB
Originally Posted by TurboBob
You need to set a reference to the Ms Excel 12.0 library
Code:
Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet
Dim strExcelFileName As String
Private Sub Command1_Click()
strExcelFileName = "C:\test.xlsx"
Set objExcel = New Excel.Application
objExcel.Visible = False
Set objWorkbook = objExcel.Workbooks.Open(strExcelFileName)
Set objWorksheet = objWorkbook.Sheets("Sheet1")' Chage this to "pivot1"
For n = 1 To 500
If objWorksheet.Range("A" & n).Value = "678" Then
MsgBox "Line " & n & " is the value you are looking for"
Exit For
End If
Next n
End Sub
Thank you it worked
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|