locked
Problem with VBA script RRS feed

  • Question

  • Dear all,

    Can you please help me with the following problem?  I wrote a script to get the data out from the SQL Server and then format the cells.  However, it seems it doesn't run the part B to format the cells.  Any idea?  Thanks.

    'Part A : Get the data from the SQL Server

    sqlstring ="select custid, custname  from customer"

    connstring = "ODBC;UID=uid;PWD=p123;DATABASE=prod;DSN=HK0001"
    With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("B1"), Sql:=sqlstring)
        .Refresh
    End With

    'Part B: Format the cells

    i = 1
    Do While ActiveSheet.Cells(1 + i, 2) <> ""
        vals = ActiveSheet.Cells(1 + i, 2).Value
        If Left(vals, 1) = "/" Then
            ActiveSheet.Cells(1 + i, 2).Value = Right(vals, Len(vals) - 1)
        End If
        i = i + 1
    Loop

    Tuesday, April 18, 2006 2:55 AM

Answers

All replies

  • Hello Roda,

    I tried the code in part B and it works fine for the data I entered. It cleared the inital / from any values I had entered. It's just cosmetic but I'd add .Value to the do while loop.

    Do While ActiveSheet.Cells(1 + i, 2).Value <> ""

    So it might be part A thats not loading the correct data. The code looks exactly how it does in the help file

    With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("B1"), Sql:=sqlstring)
        .Refresh
    End With

    What object does the .Refresh method work on here, is it the QueryTable or the ActiveSheet. It's difficult to say. Maybe this is the source of the problem. Try this instead although I haven't tested it..

        With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("B1"), Sql:=sqlstring).Refresh
        End With

    If that doesn't work I'd think about creating a QueryTable object seperately, setting it's properties, and then adding it to the collection.

    Tuesday, April 18, 2006 1:51 PM
  • Hi Roda

     

    I looked at your part B code and it should remove any leading "/". Try taking a good look at the data in the cells, is "/" the first character? Try typing =left(b2,1)="/" in an empty cell (assuming B2 looks to be preceeded by "/") if the B2 starts with "/" you will get true otherwise you will get false.

    Tuesday, April 18, 2006 2:41 PM
  • Dear all,

    Thanks very much for your reply!!!!

    Derek: I have tried this :

        With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("B1"), Sql:=sqlstring).Refresh
        End With

    but it still doesn't work. 

    Actually, here is the complete codes:

    Private Sub GetTrade_Click()

    DBquery

    Format_sheet
    End Sub

    Public Sub DBquery()

    Dim qt As QueryTable
    sqlstring = "select * from trade"
    connstring = "ODBC;DSN=HK0001;UID=id123;PWD=pw123;Database=prod
    ;dbDriverNoPrompt"
    With ActiveSheet.QueryTables.Add(Connection:=connstring, _
            Destination:=Range("B1"), Sql:=sqlstring)
        .Refresh
    End With

    End Sub


    Public Sub Format_sheet()

    ActiveSheet.Cells(1, 1) = "Trade no"
    ActiveSheet.Cells(1, 2) = "Portfolio"
    ActiveSheet.Cells(1, 3) = "Instr"
    ActiveSheet.Cells(1, 4) = "Feedcode"
    ActiveSheet.Cells(1, 5) = "Company"
    ActiveSheet.Cells(1, 6) = "Currency"
    ActiveSheet.Cells(1, 7) = "Price"
    ActiveSheet.Cells(1, 8) = "Quantity"

    i = 1
    Do While ActiveSheet.Cells(1 + i, 4) <> ""
        vals = ActiveSheet.Cells(1 + i, 4).Value
        If Left(vals, 1) = "/" Then
            ActiveSheet.Cells(1 + i, 4).Value = Right(vals, Len(vals) - 1)
        End If
        i = i + 1
    Loop

    End Sub

    When I click the button, it seems it will run the sub of the "format_sheet" first and then run the sub of "DBquery".  However, if I use the debug mode, then it is ok and doesn't have any problem.  Any idea?

    Thanks again for your help!!

     

    Wednesday, April 19, 2006 2:40 AM
  • Dear ADG,

    Thanks for your reply!!  Yes, part B code is to remove any leading "/".  I think it's not the problem of removing the "/".  If I change this part of code for a simple action, it also runs first the part A. 

    Please refer to my previous reply.  If I click on the button, it will run the part B first and then part A.  If I use the debug mode (step by step), it runs without any problem, that is runs part A and then part B.

    Is this the bug of the Excel VBA??

    Please advice.

    Thanks!!

     

    Wednesday, April 19, 2006 6:42 AM
  • Hi Roda,

    I've seen this problem before. I believe It's to do with the time it's taking to get the information from the database. When you step through the code in debug processing slows down allowing the data to be loaded fine and Part B works fine.

    If you just execute the code the data hasn't had time to load fully, part B doesn't work because the data isn't there yet.

    Try calling Format_Sheet once the QueryTable has finished refreshing.Try using code like this

    With Worksheets(1).QueryTables(1)
        If .Refreshing = False Then
            Format_sheet
        End If
    End With

    Wednesday, April 19, 2006 7:15 AM
  • Dear Derek,

    It seems doesn't work either.  I put it in the following code.

    Private Sub GetTrade_Click()

    DBquery

    With Worksheets("F7").QueryTables(1)
        If .Refreshing = False Then
            Format_sheet
        End If
    End With


    End Sub

    It still runs format_sheet first.  But it works when in debug mode just like before.

    Any other suggestion??

    Thanks.

    Wednesday, April 19, 2006 8:16 AM
  • I'm sorry Roda, never made it that clear in the last post what you needed to do. You don't want to call Format_sheet until .Refreshing is false.

    Private Sub GetTrade_Click()

    DBquery

    With Worksheets("F7").QueryTables(1)
        Do While .Refreshing = True
            DoEvents
        Loop
    End With

        Format_sheet

    End Sub

    Try that.

     

    Wednesday, April 19, 2006 10:49 AM
  • Dear Derek,

    I have tried this but it seems running forever to get the data.  I need to break it.

    However, it still working fine when I use the debug mode.

    I think the problem is : when I click the button, the codes in the sub GetTrade_Click runs.  But DBquery need to connect to the Database and it takes time.  So it runs this checking code first.  

     With Worksheets("F7").QueryTables(1)
        Do While .Refreshing = True
            DoEvents
        Loop
    End With

    However, this checking code is always true as it is connecting to the Database, so it loop forever.

    I think this is the bug of Excel VBA.  Can you try this out?  

    Anyone has other good solution.....................Please help!!

    Thanks very much!!

    Thursday, April 20, 2006 2:17 AM
  • Dear all,

    I think this is a serious problem in Excel VBA that it can't run the codes in order!!!

    Anyone in the IVS Buddy Team can help on this???  If there is no way to solve this problem, then this is the BUG!!!

     

    Cheers,

     

    Friday, April 21, 2006 1:15 AM
  • Hello again Roda,

    Pity that didin't work.

    It's very tricky to try out your code as I need to create the enviroment that its running under. I'm fairly confident that the problem is the asynchrous way the data is collected from SQL Server. There is another possibility and thats SQL Server security and the process ID of the code, there very little way to determine if this is the cause without seeing the code run first hand, and it works in debug so.... its doubtful; but still something that should be checked.

    I think it would be a good idea to start again. Try loading the data into the sheet first at runtime, check and see if the code still executes while the data is being collected, create output that shows how long it takes the data to load, and then from that decide when and where to put the code to format the data.

    Take it one step at a time and collect more information on whats actually happening.

    Best of luck getting it solved.

    Friday, April 21, 2006 9:23 AM
  • Dear all,

    I think I can't solve this problem!!!  This may due to the problem of Excel VBA when using the querytable!!  Therefore, I will not use querytable anymore but use another way to get the data like recordset. 

    BTW, where should I contact if I find that there is problem with the VBA code of Excel??

    Thanks any way, especially Derek's help!!

    Cheers,

     

    Tuesday, April 25, 2006 8:49 AM
  • Per our support engineer:

    I think the issue is related to Excels refresh way. It's a refresh issue and not a sync issue.

    I think a workaround is that we can add the method in an event of Excel:

    Private Sub Worksheet_Change(ByVal Target As Range)

    'Part B: Format the cells

    i = 1

    Do While ActiveSheet.Cells(1 + i, 3) <> ""

        vals = ActiveSheet.Cells(1 + i, 3).Value

        If Left(vals, 1) = "/" Then

            ActiveSheet.Cells(1 + i, 3).Value = Right(vals, Len(vals) - 1)

        End If

        i = i + 1

    Loop

    End Sub

    I add the code in the woksheet_change event, it can make sure the data is formatted. It can show our ISV some idea.

    -brenda (ISV Buddy Team)

    Monday, May 1, 2006 4:09 PM
  • I almost forgot... you can report suspected issues to the Product Feedback Center on http://lab.msdn.microsoft.com/productfeedback/Default.aspx?lc=1033

    -brenda (ISV Buddy Team)

    Monday, May 1, 2006 4:13 PM
  • Hi Brenda,

    I have tried your way to put the  "Part B: Format the cells"  under the worksheet_change event.  But it still doesn't work.  The excel spreadsheet hanged forever and there is a message at the bottom of the sheet " connecting to datasource........."

    Any other suggestion?   Can't your team try this out?

    Thanks anyway.

     

    Thursday, May 4, 2006 1:58 AM