Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2007
    Posts
    108

    Unanswered: Macro works in Excel 2003, but not in 2007, very irritating!

    I have a macro called from one workbook, that is supposed to:

    1. open Workbook "AllCompetitions"
    2. go through the list of links in AllCompetitions file
    3. use link to perform WebQuery
    4. extract some data
    5. perform WebQuery on link in the next row and so on....

    Code:
    Sub Nadji_sva_gotova_natjecanja()
    
    Dim Wbk_radna As Workbook
    Dim zadnjired As Integer
    Dim hlink As Hyperlink
    
    Application.ScreenUpdating = True
    
    Set Wbk_radna = Workbooks.Open("AllCompetitions")
    Wbk_radna.Worksheets(1).Activate
    Wbk_radna.EnableConnections
    
    zadnjired = Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).EntireRow.Row
    
    For i = 1 To zadnjired
        
        Call Perform_Web_Query(Wbk_radna.Worksheets(1).Cells(i, 1).Value & "fixtures/", Wbk_radna)
    
       
        Wbk_radna.Worksheets(1).Cells(i, 7).Value = "processed"
        
        For Each hlink In Wbk_radna.Worksheets(2).Hyperlinks
            
            If InStr(1, hlink.Address, "nextmatch.php") > 0 Then 
                
                Wbk_radna.Worksheets(1).Cells(i, 6).Value = "ongoing"
                Exit For
            
            End If
            
        Next hlink
    
        Wbk_radna.Worksheets(2).UsedRange.Clear
        
    Next i
    
    End Sub
    Perform_Web_Query procedure does nothng special but retrieving web data:

    Code:
    Sub Perform_Web_Query(trazeni_string As String, radna As Workbook, Optional s_popisom As Workbook)
    
    Dim hlink As Hyperlink
    
    radna.Worksheets(2).Activate
    radna.EnableConnections
    
        
        With ActiveSheet.QueryTables.Add(Connection:= _
            "URL;" & trazeni_string, Destination:=Range("$A$1"))
            .Name = "sweden"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = False
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlEntirePage
            .WebFormatting = xlWebFormattingAll
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = True
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
    
    End Sub
    I was sure there's was nothing wrong with the code so I saved the files as Excel 2003 and everything was fine and worked fine!

    So, WHAT CHARMINGLY IRRITATING FEATURE they managed to put in, what retarded security level that keeps bumping my head into the wall with message:

    Run time error 1004

    The file could not be accessed. Try one of the following

  2. #2
    Join Date
    Apr 2007
    Posts
    108
    Fascinating, how everything works in 2003 Excel without a single hickup!

    Yet, in Excel 2007 I get:

    Run time error 1004

    File could not be accessed. Try one of the following:
    - Make sure the specified folder exists
    - Make sure the folder that contains the file is not read only
    - Make sure the file name does not contain any of the following characters: < > ? [ ] : | or *
    - Make sure the path/file name does not contain more than 218 characters
    Very similar problem to the one described in http://www.ozgrid.com/forum/showthread.php?t=64645

  3. #3
    Join Date
    Apr 2007
    Posts
    108
    After dealing with the problem for quite a while all I can say is:

    Phuk Excel 2007!!!

    It's amazing how upgrading gets you:

    1. errors you didn't have in previous version
    2. brutaly pathetic help system where typing for example "Refresh" into online help gets you something like:
    Refresh
    Refresh
    Refresh
    Refresh
    Refresh
    Refresh

    ...and you are supposed to guess which Refresh applies to your problem without any help from Microsoft help (unlike before where you had Refresh > as it applies to QueryTable object)

    3. what's best you get random errors, the dream stuff of any program making process. It's not wrongly typed, it works sometimes, sometimes doesn't, you just can't be sure in which scenario and why. THE BEST!


    P.S. Going back to the original problem it seems that putting certain links inside Perform_Web_Query procedure generates "Invalid Web Query" for no apparent reason! DON'T KNOW HOW TO DEAL WITH THAT. THAT ERROR DOESN'T EXIST IN EXCEL 2003.

    Even wrapping up code in Perform_Web_Query into:

    On Error Resume Next

    Do

    ....

    Loop Until radna.Worksheets(2).Cells(1, 1).Value <> ""
    doesn't solve the problem!
    Last edited by Riorin; 10-20-08 at 18:30.

Posting Permissions

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