CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2011
    Posts
    5

    Unhappy 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

  2. #2
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    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.

  3. #3
    Join Date
    Feb 2011
    Posts
    5

    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

  4. #4
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: looping problem VB

    Use a SQL connection, instead
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  5. #5
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    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.

  6. #6
    Join Date
    Feb 2011
    Posts
    5

    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
    '----------------------------------------------------------------

  7. #7
    Join Date
    Feb 2011
    Posts
    5

    Re: looping problem VB

    Quote Originally Posted by dglienna View Post
    Use a SQL connection, instead

    Any how do I do that instead?

  8. #8
    Join Date
    Mar 2010
    Posts
    26

    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

  9. #9
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    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.

  10. #10
    Join Date
    Feb 2011
    Posts
    5

    Red face Re: looping problem VB

    Quote Originally Posted by TurboBob View Post
    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
  •  





Click Here to Expand Forum to Full Width

Featured