Problem with VBA script

Problem with VBA script

• H. Gøttig

  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)

  
  
  

• Komyg

  Per our support engineer:

  I think the issue is related to Excel’s 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)

  
  
  

• Chris Vance

  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,

  
  
  

• george22

  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.

  
  
  

• Uwe Lesta

  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.

  
  
  

• Muzzzy

  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
  

  
  
  

• Fordy

  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.

  
  
  

• CSH

  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.

  
  
  

• Sam Nadeau

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

  
  
  

• Ravs Kaur _ MS

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

  
  
  

• kalkie

  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,

  
  
  

• atmaj.desai

  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.

  
  

• wessamzeidan

  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.

  
  
  

• dihoch

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

  
  
  
• Problem with VBA script