Compare Proposal

Nothing to compare.

Excel VBA - Fix QueryTables issue

  • Posted at : 1 month ago
  • Post Similar Project
500

Budget
7
Proposals
112
Views
Expired
Status
Skills Required

Posted By -

TT

5.0
Projects Posted : 7
Projects Paid : 4
Services Purchased : 0
Total Spent :
87
Feedbacks : 100 %

Project Details show (+) hide (-)

I need a long term highly skilled Excel VBA developer.

First task is to fix a query string issue. Following function gives error related to  formula.
Please apply if you have worked with QueryTables before and you are sure that you can fix this.
I will provide macro file once I see some confidence in you and also assign project.

Sub downloaddata()
    On Error GoTo ErrOccured
   
    strEndYr = Trim(Str(Year(Sheets("Main").Range("L1").Value)))
    strStartYr = Trim(Str(Year(Sheets("Main").Range("L3").Value)))
    strEndDt = Trim(Str(Day(Sheets("Main").Range("L1").Value)))
    strStartDt = Trim(Str(Day(Sheets("Main").Range("L3").Value)))
    strEndMo = Trim(Str(Month(Sheets("Main").Range("L1").Value) - 1))
    strStartMo = Trim(Str(Month(Sheets("Main").Range("L3").Value) - 1))
    intDownloadRecords = Sheets("Main").Range("L2").Value
    If Len(strEndMo) = 1 Then strEndMo = "0" & strEndMo
    If Len(strStartMo) = 1 Then strStartMo = "0" & strStartMo
    strFormula = "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(Web.Contents(""" & ""function=TIME_SERIES_DAILY_ADJUSTED"" & ""&datatype=csv"" & ""&symbol=" & strSymbol & """ & ""&outputsize=full"" & ""&apikey=N2T7KBUCA0A5ZEZP""),[Delimiter="","", Columns=9, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScal" & _
                 "ars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""timestamp"", type date}, {""open"", type number}, {""high"", type number}, {""low"", type number}, {""close"", type number}, {""adjusted_close"", type number}, {""volume"", Int64.Type}, {""dividend_amount"", type number}, {""split_coefficient"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Change" & _
                 "d Type"""

    strFormula2 = "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & strDisplay


    ActiveWorkbook.Queries.Add Name:=strDisplay & Now, Formula:=strFormula
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(strFormula2), Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array( _
                       "SELECT TOP " & intDownloadRecords & " * FROM [" & strDisplay & "]" _
                       )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = strDisplay
        .Refresh BackgroundQuery:=False
    End With
   
ErrOccured:
End Sub

This is just a first small job. This may lead to many more VBA projects in future.

Thanks.

Your Job Feed