none
Run-time error 438- HELPPP!! RRS feed

  • Question


  • The 'Filepath' does not read "URL" and "clientName". Runtime error 438
    Private Sub temp()

    Dim Qa As Workbook
    Dim temp As Workbook
    Dim sum As Worksheet
    Dim cov As Worksheet
    'Dim ClientDate1 As Date
    'Dim ClientDate2 As Date

    'Dim Url As String
    'Dim FilePath As String


    Set Qa = ThisWorkbook
    Set temp = Workbooks.Add

    Set sum = Qa.Worksheets("Summary")
    Set cov = Qa.Worksheets("Coverage")
    ClientName = sum.Range("A1").Value
    ClientDate = sum.Range("A3").Value
    ClientName = sum.Cells(1, 1)

    ClientDate1 = Format(ClientDate, "mm")
    ClientDate2 = Format(ClientDate, "yyyy")


    Url = "http://wiki.rosslyn.local/mediawiki/index.php/"
    FilePath = "Url" & "ClientName" & "_" & ClientDate2 & "_" & ClientDate1
     With ActiveSheet.QueryTables.Add(Connection:= _
             "FilePath;", _
            Destination:=temp.cov.Range("$A$1").Value)
            .Name = "Deloitte_2013_08"
            .CommandType = 0
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlEntirePage
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False

        End With


        
      temp.Close
        
        End Sub
    Monday, September 30, 2013 3:22 PM

All replies

  • Remove the quotation marks from around ClientName.
    From...
    "Url" & "ClientName" &
    To...
    "Url" & ClientName &
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Sunday, October 30, 2016 7:48 PM
    Wednesday, October 2, 2013 11:34 AM