DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 3 of 4 FirstFirst 1234 LastLast
Results 31 to 45 of 52

Thread: Several Excel VB Questions.

  1. #31
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    Do you have 10 querytables?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

  2. #32
    Join Date
    Jun 2007
    Posts
    39
    Code:
        With ActiveSheet.QueryTables.Add(Connection:= _
            "URL;" & QueryURL, Destination:=Range("A1"))
            .Name = "QueryCheck"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = False
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingNone
            .WebTables = QueryCheck   ' HERE <--------------------Tables
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
    When you run a WebQuery it grabs specified "WebTables" well the first time I run the earlier Code it returns Table 10 for the Table that has "in stock" on it.. The next time it runs it returns Table 1 having the information.. Somewhere It wasn't clearing something.. So what I'm going to do is put that clear code you sent me into another Sub and Call it to clear all query's from the sheet I'm working with.. Cause your right.. there may be more then one query hiding on the page..

  3. #33
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    Yes, it could be from running test or debuging code that you stop in the middle and it doesnt delete or such. etc.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

  4. #34
    Join Date
    Jun 2007
    Posts
    39
    except all my deletes are at the begining of the Sub.. and I hit Stop then Play before I restart...

    I might have it figured out.. Still running Tests.. hehe I get weard outputs when I delete on of the sheets.. I might be deleting it at the wrong point..

  5. #35
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    Instead of clicking "play" (the run button) you can execute a single line of code one at a time by pressing F8 after you place a breakpoint at the top of your procedure. The breakpoint will pause execution so you can proceed with F8 keypresses.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

  6. #36
    Join Date
    Jun 2007
    Posts
    39
    ahh Yes I know about the Step In hehe It doesn't always tell me what i need to know.. How do I view the Variable settings after each line is executed? I need to be able to actually see more of the process to properly debug it... But I can't find a "Variable" List..

  7. #37
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    Mouse over the varibales or object properties to see thirer values in the tooltip. Or you can use the immediate window and do a ....


    ?SomeVar

    and then press enter and it will display the variables value.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

  8. #38
    Join Date
    Jun 2007
    Posts
    39
    Awesome! Ok one more question for now hehe

    I need to cycle through a Query and find the last Cell with Data in it.. Here is my CellCheck Sub... How Might I do that?

    Code:
    Sub ClearSheets(Sheet_Name)
    Sheets(Sheet_Name).Select
    Cells.Select
    Selection.ClearContents
    
        If ActiveSheet.QueryTables.Count > 0 Then
            Dim i As Integer
            For i = ActiveSheet.QueryTables.Count To 1 Step -1
                ActiveSheet.QueryTables.Item(i).Delete
            Next
        End If
        Selection.ClearContents
    End Sub
    
    Sub CellCheck(Check_Sheet, Check_Match, Check_Num, Max_Row, Max_Column, Msg)
    
    Dim Check_Text As String
    Match = "False"
    Check_Text = "Will Never Match, My Neo Wooga Wooga"
        Sheets(Check_Sheet).Select
    For CR = 1 To Max_Row Step 1
        For CC = 1 To Max_Column Step 1
    
            If Cells(CR, CC) = 0 Then GoTo EndLoop
            Check_Text = Right(Cells(CR, CC), Check_Num)
            If Check_Text = Check_Match Then GoTo Check_Done
            Check_Text = Left(Cells(CR, CC), Check_Num)
            If Check_Text = Check_Match Then GoTo Check_Done
    
    EndLoop:
        Next CC
    Next CR
    
    ' Failed Check
        If Msg = "NotFound" Then Exit Sub Else MsgBox Msg
        Exit Sub
            
    ' Passed Check
    Check_Done:
        Match = "True"
    End Sub
    Here is a Peice of the WebTable that is returned..

    Scratching Post Red Paint Brush Yellow Paint Brush Green Paint Brush
    3 in stock 1 in stock 1 in stock 1 in stock
    Cost : 6,400 NP Cost : 72,600 NP Cost : 65,000 NP Cost : 79,000 NP


    Blue Paint Brush Glass Paint Brush Stone Paint Brush Angelpuss
    1 in stock 1 in stock 1 in stock 3 in stock
    Cost : 78,000 NP Cost : 99,999 NP Cost : 80,000 NP Cost : 10,800 NP


    Warf Buzzer Snorkle Flishy
    2 in stock 2 in stock 2 in stock 2 in stock
    Cost : 13,000 NP Cost : 5,000 NP Cost : 20,000 NP Cost : 24,000 NP


    Greeble Cobrall Tigermouse Puppyblew
    2 in stock 5 in stock 2 in stock 3 in stock
    Cost : 4,000 NP Cost : 4,000 NP Cost : 20,000 NP Cost : 10,200 NP


    Spyder Triffin Mallard Poppit
    6 in stock 1 in stock 10 in stock 2 in stock
    Cost : 3,700 NP Cost : 88,000 NP Cost : 3,500 NP Cost : 6,000 NP


    Uniocto Spoppy Moink Pinceron
    2 in stock 2 in stock 2 in stock 2 in stock
    Cost : 6,000 NP Cost : 7,900 NP Cost : 47,000 NP Cost : 22,000 NP


    Diddler Wheelie Pfish Leeble
    3 in stock 2 in stock 2 in stock 2 in stock
    Cost : 12,000 NP Cost : 10,000 NP Cost : 12,000 NP Cost : 12,000 NP


    Tanizard Pepito Baby Blu Frillabon
    2 in stock 2 in stock 2 in stock 2 in stock
    Cost : 60,000 NP Cost : 12,000 NP Cost : 30,000 NP Cost : 9,995 NP


    Cubett Bubblisaur Filamen Peo
    3 in stock 3 in stock 2 in stock 1 in stock
    Cost : 10,000 NP Cost : 12,400 NP Cost : 20,000 NP Cost : 19,000 NP


    3 Rows and 4 Columns of Data then a 2 line Gap.. then 3 Rows and 4 Columns again...

    so.. A3 - D5, A8 - D10, A13 - D15 has Data there's a two line gap between each set of three...
    Last edited by RedSatiin; 07-03-2007 at 06:40 PM.

  9. #39
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    To find the last used row in a sheet you can use one of two methods.

    SpecialCells or UsedRange
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

  10. #40
    Join Date
    Jun 2007
    Posts
    39
    ok SpecialCells worked Mint for deleting the unused cells.. But I don't see any reference to how to use UsedRange and in the book I have it doesn't say how to use SpecialCells to find the last used one..

  11. #41
    Join Date
    Jun 2007
    Posts
    39
    Code:
    Range("A1:D200").SpecialCells(xlCellTypeBlanks).Delete
    Range("A1:D80").SpecialCells(xlCellTypeLastCell).Select
    The First one works right.. But the Second one Selects Cell L138 :S it's not even in the range I choose..

  12. #42
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    Thats always been an issue from I have experienced. Probably a bug but SpecialCells only seems to work correctly for last row when detecting entire rows and not rows in a specified range.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

  13. #43
    Join Date
    Jun 2007
    Posts
    39
    ok I worked it out with my CellCheck Routine

  14. #44
    Join Date
    Jun 2007
    Posts
    39
    I know I'm doing this wrong :S But I can't figure it out...

    Range("A" & MyNeof & ":D" & MyNeof).Cut Sheets("Temp2").Range("A1").Insert(xlShiftDown)


    I need to Cut A Something Through D Something and Insert it to a New Sheet("Temp") In A1 and Shift everything down...

    Can you help me with the Syntax?
    Last edited by RobDog888; 07-03-2007 at 09:48 PM. Reason: Disabled smilies in post

  15. #45
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    It looks ok but without fully qualifying the Range object it will depend upon which sheet you place that code behind.

    What is the value of MyNeof ? You are cutting something like "A11" ?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

Similar Threads

  1. VB to excel export
    By Ananthakrishnan in forum VB Classic
    Replies: 3
    Last Post: 05-20-2005, 11:01 AM
  2. Excel and VB
    By svn in forum VB Classic
    Replies: 6
    Last Post: 05-17-2005, 09:55 AM
  3. They created J#, why couldn't they do VB#?
    By Thomas Eyde in forum .NET
    Replies: 290
    Last Post: 12-22-2001, 03:13 PM
  4. Replies: 84
    Last Post: 01-29-2001, 02:12 PM
  5. Re: VB or Powerbuilder?
    By Jean-Yves in forum Enterprise
    Replies: 3
    Last Post: 06-01-2000, 12:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center