DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 52

Thread: Several Excel VB Questions.

  1. #16
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    1. Use the .Activate method.

    Application.Workbooks("SomeChoosenWorkbookName").Activate


    2. You can add a hidden property to each sheet and when loadedd you can right after verify or set the property with the name of the sheet. Then the user can rename the sheet until their hearts content but you can always still refer to the sheet from its hidden original property value.

    Workbooks(1).Sheets(1).CustomProperties.Add Name:="OriginalName", Value:=Workbooks(1).Sheets(1).Name


    3. To find a last row or column in a range you can use the .SpecialCells function. Pass the xlCellTypeEmpty.


    Next?
    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. #17
    Join Date
    Jun 2007
    Posts
    39
    ok #2 I placed it in the Declarations and made a sub FindSheet This what I got..

    Sheet3(Your Shop)
    Declarations: Workbooks(1).Sheets(3).CustomProperties.Add Name:="UserShopStock", Value:=Workbooks(1).Sheets(3).Name

    Module4: Sub FindSheet()

    Sheets(UserShopStock).Select

    End Sub


    ERROR: "Invalid Outside Procedure" Highlighted: Sheet3(Your Shop) "UserShopStock"

  3. #18
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    Your Shop whould be wrapped with double quotes as its not a variable but the sheet name.

    Sheet3("Your Shop")

    As well as
    Sheets("UserShopStock").Select
    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. #19
    Join Date
    Jun 2007
    Posts
    39
    Ok I still have :

    Workbooks(1).Sheets(3).CustomProperties.Add Name:="UserShopStock", Value:=Workbooks(1).Sheets(3).Name

    In the Declarations for "Sheet3(Your Shop)" Note in the browser it doesn't have Quotes.. So I try to run a different Macro.. Has nothing to do with the above statement.. And I get the Error "Invalid Outside Procedure"

  5. #20
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    If you are running a different macro then perhaps that different macro has an error. Can you post more of your code?
    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. #21
    Join Date
    Jun 2007
    Posts
    39
    Doesn't matter what macro I run I get an Error for that peice.. Every Macro that is run gives an error of "Invalid Outside Parameters" For that code I pasted.. I don't get any errors when I remove that peice of code..



    Another Question.. I have a Query Table on a sheet..I have a macro that creates new web queries to check web site things.. How do I Selection.QueryTable.Delete in a If statement..

    Explaination: On Sheet("Temp") I do my Sheet calculations.. Some Macro's don't use Query Tables.. Others do.. I need to be able to ClearContents of the Temp Sheet Including the QueryTable IF there is a Table.. If I use Selection.QueryTable.Delete when there is no table there I get a pop up Error.. If I don't use it when there is a Query it moves Old Data to the Right.. Placing way to much info on the sheet.

  7. #22
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    For the "Invalid Outside Parameters" error it is hard to say since it may be dependant upon other code in that macro.

    You can use the QueryTables collection to check for the existance of your qt.


    Code:
    If ActiveWorkbook.QueryTables.Count > 0 Then
        If ActiveWorkbook.QueryTables.Item(1).Name = "Something" Then
            ActiveWorkbook.QueryTables.Item(1).Delete
        End If
    End If
    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. #23
    Join Date
    Jun 2007
    Posts
    39
    For the "Invalid Outside Parameters" error it is hard to say since it may be dependant upon other code in that macro.

    There is no macro for that.. Does that need to be inside a sub? How do you have it set the name upon open? Without getting the display about macro's running on open?

  9. #24
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    Yes, that code and any code that is to be executed/run needs to be inside a procedure of some kind.

    Code:
    Public Sub SetMyProperty()
        Workbooks(1).Sheets(3).CustomProperties.Add Name:="UserShopStock", Value:=Workbooks(1).Sheets(3).Name
    End Sub
    Its just hard to see where you have your code without seeing it.
    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. #25
    Join Date
    Jun 2007
    Posts
    39
    If ActiveWorkbook.QueryTables.Count > 0 Then
    If ActiveWorkbook.QueryTables.Item(1).Name = "Something" Then
    ActiveWorkbook.QueryTables.Item(1).Delete
    End If
    End If

    Doesn't support QueryTable.Count hehe But I fixed it.. it should be

    If ActiveSheet.QueryTables.Count > 0 Then
    ActiveSheet.QueryTables.Item(1).Delete
    End If

  11. #26
    Join Date
    Jun 2007
    Posts
    39
    Quote Originally Posted by RobDog888
    Yes, that code and any code that is to be executed/run needs to be inside a procedure of some kind.

    Code:
    Public Sub SetMyProperty()
        Workbooks(1).Sheets(3).CustomProperties.Add Name:="UserShopStock", Value:=Workbooks(1).Sheets(3).Name
    End Sub
    Its just hard to see where you have your code without seeing it.

    Woohoo hehe

  12. #27
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    You can change it to a function so you can pass in the sheet name or index so its not all hard coded.

    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. #28
    Join Date
    Jun 2007
    Posts
    39
    ok This is going to be big.. :S Cause I'm just confussed..

    I run my Query Check and then check Text in the Query (Looking for a specified Table) And first time I run it it returns Table 10.. Next time I run it it return Table 1. Something isn't clearing and I just don't understand what...

    Code:
    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
    ' Increase Row and Column, Check Max
    
    For CR = 1 To Max_Row Step 1
        
        For CC = 1 To Max_Column Step 1
            
    ' Disect String
            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
    
    
    
    
    Sub RunQueryCheck(QueryURL, Max_Table)
            
        Cells.Select
        Selection.ClearContents
        If ActiveSheet.QueryTables.Count > 0 Then
            ActiveSheet.QueryTables.Item(1).Delete
        End If
    
     For QueryCheck = 1 To Max_Table Step 1
        Cells.Select
        Selection.ClearContents
        If ActiveSheet.QueryTables.Count > 0 Then
            ActiveSheet.QueryTables.Item(1).Delete
        End If
        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
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
        MsgBox "Pause"
        Call CellCheck(Check_Sheet, Check_Match, Check_Num, Max_Row, Max_Column, Msg)
        If Match = "True" Then
            MsgBox Match & CC & CR
            MsgBox QueryCheck
            Exit Sub
        End If
        MsgBox QueryCheck & " Checking..."
    Next QueryCheck
    End Sub
    The MsgBox's are for me to Debug.. Here's the Call for it...

    Code:
    ' Cell Check Variables
        Check_Sheet = "Temp"
        Check_Match = "in stock"
        Check_Num = 8
        Max_Row = 5
        Max_Column = 1
        Msg = "NotFound"
    ' RunQueryCheck Variables
        URL = "http://www.neopets.com/browseshop.phtml?lower=0&owner=" & MyneoShopOwner
    
    Call RunQueryCheck(URL, 12)
    I'm trying to make the first two Subs up there callable from different Macro's.. So I don't have to rewrite them for each Macro that will need them... But it's not very stable as is

  14. #29
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    Ok, if you are trying to delete all querytables then we need to iterate through the collection deleting each one in a loop.

    Code:
    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
    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

  15. #30
    Join Date
    Jun 2007
    Posts
    39
    Shouldn't that reset the page? but as it appears it actually refreshes the previous web query.. which at that time is Table 10.. It's like Every other it messes up.. First finds it at Table 10.. Next time Finds at Table 1.. Then Table 10.. so on so forth...

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