Posts by dojorgen

  • Application.OnTime not working after converting from Excel 2010 to Excel 2016

    My simple run requests were working just fine until the 2016 excel upgrade. I tried to enable and disable options but nothing seems to be working.


    Workbook:
    Private Sub Workbook_Open()
    Application.OnTime TimeValue(13:30:00), "Refresh"
    Application.OnTime TimeValue(13:31:00), "Shutdown"
    End Sub


    Module:
    Sub Refresh()
    ActiveWorkbook.RefreshAll
    End Sub


    Sub Shutdown()
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    End Sub

  • 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

  • fixing a looping web query macro that freezes

    I have a similar problem as per


    https://www.ozgrid.com/forum/f…-macro-that-freezes/page2


    I'm pulling 1800+ web connects at one time and would need to perform this cycle every 30 minutes or so. I tested it out using web connects but can never make it through the cycle without it freezing up on me. Looks like the best solution might be to use XMLhttp but is seems like you need a good background in web tables which i do not have. The information i am trying to pull is from 'Table 2'. How can i get it to pull the correct table information?
    [ATTACH=JSON]{"data-align":"none","data-size":"full","data-tempid":"temp_71690_1520029804257_846","title":"FY18WW48lotscan.png"}[/ATTACH]

  • Fixing a looping web query macro that freezes

    How do i get this conversation started up again, i have the same problem of the program crashing. I'm pulling 1800+ web connects at one time and I would need to do it about every 30 minutes or so. I have high excel skill but low VBA skill. Looks like the best way to ensure no freezing issues is to pull XMLhttp but i am no familiar with how to set those up. I think i had it working put not pulling anything because I think my web tables are not right. Because nothing populates when I look at the web page the data I need to pull is from a Table 2 reference.


    Any help would be apprecaitive


    [ATTACH=JSON]{"data-align":"none","data-size":"full","data-attachmentid":1200264}[/ATTACH]

  • Fixing a looping web query macro that freezes

    How do i get this conversation started up again, i have the same problem of the program crashing. I'm pulling 1800+ web connects at one time and I would need to do it about every 30 minutes or so. I have high excel skill but low VBA skill. Looks like the best way to ensure no freezing issues is to pull XMLhttp but i am no familiar with how to set those up. I think i had it working put not pulling anything because I think my web tables are not right. Because nothing populates when I look at the web page the data I need to pull is from a Table 2 reference.


    Any help would be apprecaitive


    [ATTACH=JSON]{"data-align":"none","data-size":"full","data-attachmentid":1200262}[/ATTACH]