Dynamic Link

  • Any help would be appreciated, Macro2 works fine but it is a static report, Macro1 does not work properly trying to make it a dynamic report (mystr = 20180222; i.e. date)


    Any ideas on how to make Macro1 work properly?



    Sub Macro1()


    Dim mystr As String
    For x = 1 To 1
    Worksheets("Sheet1").Select
    mystr = Cells(x + 1, 3)




    Worksheets("Sheet2").Select


    ActiveWorkbook.Queries.Add Name:=mystr & "_vfei_123456 log", Formula:= _
    "let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(""R:" & mystr & "_vfei_123456.log.1""),[Delimiter="":"", Columns=4, Encoding=1252,
    QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", Int64.Type}, {""Column3"", type text}, {""Column4"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=mystr & ""_vfei_123456 log"";Extended Properties=""""" _
    , Destination:=Range("$A$1")).QueryTable
    .CommandType = xlCmdSql
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "_" & mystr & "_vfei_123456_log"
    .Refresh BackgroundQuery:=False
    End With
    Next


    End Sub





    Sub Macro2()



    ActiveWorkbook.Queries.Add Name:="20180222_vfei_123456 log", Formula:= _
    "let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(""R:\20180222_vfei_123456.log.1""),[Delimiter="":"", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", Int64.Type}, {""Column3"", type text}, {""Column4"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""20180222_vfei_123456 log"";Extended Properties=""""" _
    , Destination:=Range("$A$1")).QueryTable
    .CommandType = xlCmdSql
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "_20180222_vfei_123456_log"
    .Refresh BackgroundQuery:=False
    End With
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!