+ Reply to Thread
Results 1 to 2 of 2

Hide, unhide and make web queries

Hybrid View

  1. #1
    Registered User
    Join Date
    07-11-2011
    Location
    Santa Catarina , Brazil
    MS-Off Ver
    Excel 2003
    Posts
    3

    Question Hide, unhide and make web queries

    Hi,

    I've a workbook with 20 web queries trough 4 sheets (total of 5 sheets but one doesn't have any queries). I needed a macro to change the URL based on cell contents , so I could change the data I was getting, but then I had to hide the queries cause they just dont look good (I have to leave too much space betweem them or they will overwrite each other) and for the macros to work properly , I have to unhide them, do what the macro does and them hide them again, but for this one macro its just not working. Follows the code (I've changed the URLs for privacy issues):
    For character count issues I'll only post the code of 1 query per sheet for ther 3 mopst importante sheets and make these:

     .Name = "Query"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = True
            .BackgroundQuery = True
            .RefreshStyle = xlOverwriteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlNone
            .WebTables = "1"
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
    Into this :
    Group 1
    Sub Make()
    
    '   Shows hidden queries
    
        Sheets("E.Camila").Select
        Cells.Select
        Range("K1").Activate
        Selection.EntireColumn.Hidden = False
        Sheets("E.Bianca").Select
        Cells.Select
        Range("K1").Activate
        Selection.EntireColumn.Hidden = False
        Sheets("E.Silvia").Select
        Cells.Select
        Range("K1").Activate
        Selection.EntireColumn.Hidden = False
      
        '  Makes the queries
    
        With Sheets("E.Camila").QueryTables.Add(Connection:= _
            "http://xxx.xxxxxxx.xxxxxxxxxxx.php?dd" & Sheets("Master").Range("i17") & "&dm=" & Sheets("Master").Range("i18") & "&da=2011&" & _
            "ad=" & Sheets("Master").Range("i19") & "&am=" & Sheets("Master").Range("i20") & "&aa=2011&novorepresentante=2277&novocliente=&notrega[]=FATURADO&notrega[]=0000-00-00&notrega[]=2011-05-21&notrega[]=2011-05-26&notrega[]=2011-06-30&notrega[]=2011-07-04&notrega[]=2011-07-15&notrega[]=2011-07-30&notrega[]=2011-08-30", _
            Destination:=Sheets("E.Camila").Range("$A$2"))
    Group 1
        End With
    
    
       
    
    
        With Sheets("E.Bianca").QueryTables.Add(Connection:= _
            "URL;http://xxx.xxxxxxx.xxxxxxxxxxx.php?dd=" _
            & Sheets("Master").Range("i17") & "&dm=" & Sheets("Master").Range("i18") & "&da=2011&" & "ad=" & Sheets("Master").Range("i19") & "&am=" & Sheets("Master").Range("i20") _
            & "&aa=2011&novorepresentante=413&novocliente=&notrega[]=FATURADO&notrega[]=0000-00-00&notrega[]=2011-05-21&notrega[]=2011-05-26&notrega[]=2011-06-30&notrega[]=2011-07-04&notrega[]=2011-07-15&notrega[]=2011-07-30&notrega[]=2011-08-30", _
            Destination:=Sheets("E.Bianca").Range("$A$452"))
            .Name = "Query"
    Group 1
        End With
    
    With Sheets("E.Silvia").QueryTables.Add(Connection:= _
            "URL;http://xxx.xxxxxxx.xxxxxxxxxxx.php?dd=" _
            & Sheets("Master").Range("i17") & "&dm=" & Sheets("Master").Range("i18") & "&da=2011&" & "ad=" & Sheets("Master").Range("i19") & "&am=" & Sheets("Master").Range("i20") _
            & "&aa=2011&novorepresentante=448&novocliente=&notrega[]=FATURADO&notrega[]=0000-00-00&notrega[]=2011-05-21&notrega[]=2011-05-26&notrega[]=2011-06-30&notrega[]=2011-07-04&notrega[]=2011-07-15&notrega[]=2011-07-30&notrega[]=2011-08-30", _
            Destination:=Sheets("E.Silvia").Range("$A$2"))
            .Name = "Query"
    Group 1
        End With
        
    
    
        
    ' Hides queries again
        
        Sheets("E.Camila").Select
        Columns("A:J").Select
        Selection.EntireColumn.Hidden = True
        Sheets("E.Bianca").Select
        Columns("A:J").Select
        Selection.EntireColumn.Hidden = True
        Range("L1").Select
        Sheets("E.Silvia").Select
        Columns("A:J").Select
        Selection.EntireColumn.Hidden = True
        Range("L1").Select
        Sheets("Master").Select
        Range("I6").Select
            
    End Sub

  2. #2
    Registered User
    Join Date
    07-11-2011
    Location
    Santa Catarina , Brazil
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Hide, unhide and make web queries

    ' I've chaged the: 
    
     .webformatting = xlnone 
     
    ' to:
    
    .webformatting = xlRTF
    Still not going well.
    Last edited by Jacafio; 07-13-2011 at 10:15 AM. Reason: Forgot to say it didnt solve it

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1