用于引用某个单元格以将日期输入URL的参数

I have a workbook that is pulling data for every hour of the day from an internal website. The VBA script is built to pull data 24 times (each hour).

我有一个工作簿,可以从内部网站提取当天每小时的数据。 VBA脚本用于将数据拉出24次(每小时)。

What I'm trying to do is set up a parameter that will reference a certain cell (Master!K5) which will contain the date I need to pull. I want to be able to have that cell referenced automatically and input the date for each URL in the VBA script.

我要做的是设置一个参数,引用某个单元格(Master!K5),它将包含我需要拉的日期。我希望能够自动引用该单元格,并在VBA脚本中输入每个URL的日期。

Is this a possibility?

这有可能吗?

With Sheets("C 0000-0100").Activate
        Cells.Select
        Selection.ClearContents
   End With
   With CaseHour01.QueryTables.Add(Connection:= _
        "URL;https://xxxx-xxxxx.xxxxxx.com/reports/functionRollup?reportFormat=HTML&warehouseId=xxxx&processId=1002967&startDateDay=2014/04/01&maxIntradayDays=1&spanType=Intraday&startDateIntraday=2014/04/01&startHourIntraday=0&startMinuteIntraday=0&endDateIntraday=2014/04/01&endHourIntraday=1&endMinuteIntraday=0 " _
        , Destination:=Range("'C 0000-0100'!$A$1"))
        .Name = "00.000&reportFormat=HTML&processId=01002967"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = """function-4300006644"""
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
   End With

1 个解决方案

#1

I was able to locate a solution after an extensive google search. Here is what I found:

经过广泛的谷歌搜索,我能够找到解决方案。这是我发现的:

When a date appears in a URL, enter

当日期出现在URL中时,请输入

" & Format(Range("'Sheet1'!E2"), "yyyy/mm/dd") & "

where the date should be. This will allow you to select a certain Cell and input that value in the URL. (You would change the format of the date based on how it appears in the original URL.

日期应该在哪里。这将允许您选择某个Cell并在URL中输入该值。 (您可以根据日期在原始网址中的显示方式更改日期格式。

#1