VBA-日期减去从今天起的X天 [英] VBA - date minus X days from today

问题描述

这是我的发票生成器的摘要，尤其是爬网程序SUB，它从URL包含汇率日期的网页上获取汇率.我正在尝试如果引用日期(datIzd，从datIzdCtrl内容控件中拉出)是星期日或星期一，则强制它从星期六开始进入URL，因为那一天尚未生成页面.当前，星期日和星期一发出超出范围"错误.

Here's a snippet of my invoice generator, particularly the crawler SUB that grabs the exchange rate from a webpage who's URL contains the exchange rate date. I'm trying is force it to go to a URL from Saturday if the referring date (datIzd, pulled from datIzdCtrl content control) is Sunday or Monday, because the page isn't yet generated on those days. Currently Sunday and Monday send out "out of range" error.

也许更好的方法是让日期继续前进1天，直到它碰到一个存在的页面(并在MsgBox中通知它前进了多远)，因为同样的事情适用于假期-银行不适用释放新汇率，因此与上一个工作日的汇率有关.

Perhaps a much better way would be for the date to keep going 1 day back until it hits a page that exists (and notifying in MsgBox how far back it went), because the same thing applies to holidays - bank doesn't release the new exchange rate, so the one from last working day is relevant.

有人可以告诉我这是怎么做的吗?我尝试使用

Can someone show me how this is done? I tried using

If Weekday(Now(), vbMonday)

并尝试使用它，但是并没有走远.

and playing around with it, but it didn't get far.

我也知道，在代码中，我似乎不必要多次重新设置日期，但这是必须的，因为美国和克罗地亚的日期格式不一样，而且必须在相应的发票上正确显示它们，并重新计算两次转换之间的URL名称.

Also I'm aware that in the code I'm seemingly unnecessarily reformating dates multiple times, but this is a must since US and Croatian date formats aren't the same, and they have to be properly presented on respective invoices, and recalculated for URL name between conversions.

这就是我所拥有的.

Dim splData As Variant

Enum READYSTATE
    READYSTATE_UNINITIALIZED = 0
    READYSTATE_LOADING = 1
    READYSTATE_LOADED = 2
    READYSTATE_INTERACTIVE = 3
    READYSTATE_COMPLETE = 4
End Enum

Sub Crawler()

    Dim url As String, datIzd As Date, xmlHTTP As MSXML2.ServerXMLHTTP60
    Dim getData As String

    Set xmlHTTP = New MSXML2.ServerXMLHTTP60

    ActiveDocument.SelectContentControlsByTitle("datIzCtrl")(1).Range.ParentContentControl.DateDisplayFormat = "MM-DD-YYYY"

    datIzd = ActiveDocument.SelectContentControlsByTitle("datIzCtrl")(1).Range.Text

    With xmlHTTP
        url = "http://www.hnb.hr/tecajn/f" & Format(datIzd, "ddmmyy") & ".dat"
        .Open "GET", url, False
        .setRequestHeader "Content-Type", "text/xml"
        .send
        getData = .responseText
    End With

    repData = Replace(getData, "       ", vbCrLf)
    repData = Replace(getData, "      ", vbCrLf)
    splData = Split(repData, vbCrLf)

    If OptionPredracun.Value = True Or OptionRacunPredujam.Value = True Or OptionRacunUkupniIznosHR.Value = True Then
        ActiveDocument.SelectContentControlsByTitle("datIzCtrl")(1).Range.ParentContentControl.DateDisplayFormat = "DD. MMMM YYYY."
    Else
        ActiveDocument.SelectContentControlsByTitle("datIzCtrl")(1).Range.ParentContentControl.DateDisplayFormat = "MMMM DD, YYYY"
    End If


End Sub

注意:如果这听起来令人困惑，您可以查看我以前关于同一项目的帖子，其中更详细地介绍了我想做的事情:

Note: if this sounds confusing, you can have a look at my previous post regarding this same project that goes into more detail on what I'm trying to do:

VBA提取并将网站中的数据解析为Word

推荐答案

关于变量 datIzd 的类型还不清楚.表单的.Text属性的赋值似乎是一个 String ，但后来在.Format函数中使用它使它看起来像 Variant 或日期类型.在下文中，我将其声明为 Date 类型，并使用DateValue(另一个选项为CDate)将内容控件的文本转换为实际日期.

It is a bit unclear on what type of variable datIzd is. The assignment to the .Text property of the form seems that it is a String but its later use in the .Format function makes it look like a Variant or Date type. In the following, I'll declare it as a Date type and use DateValue (another option is CDate) to convert the content control's text to an actual date.

dim datIzd as date
datIzd = DateValue(ActiveDocument.SelectContentControlsByTitle("datIzCtrl")(1).Range.Text)

一旦您在 datIzd 中有一个实际日期，就应该能够确定其Weekday并从日期中减去工作日(如果它是星期日或星期一).默认的vbSunday可能是最好的选择，因为它使Sunday为 1 ，而Monday为 2 .

Once you have an actual date in datIzd, you should be able to determine its Weekday and subtract the weekday from the date if it is Sunday or Monday. The default vbSunday is probably best for this as that makes Sunday a 1 and Monday a 2.

if Weekday(datIzd) < 3 then
  datIzd = datIzd - Weekday(datIzd)
end if

这样的简化版本使用VBA的 -1/0 值表示 True/False ，如下所示.

A more simplified version of that uses VBA's perceived values of -1/0 for True/False like this.

datIzd = datIzd + ((Weekday(datIzd) < 3) * Weekday(datIzd))

应该在使用 datIzd 在其中创建URL字符串之前完成所有操作

This should all be done before datIzd is used to create the URL string in,

url = "http://www.hnb.hr/tecajn/f" & Format(datIzd, "ddmmyy") & ".dat"

其中任何一个都应将星期日和星期一推回到上一个星期六.周二及以后将不予理会.

Either of those should push Sunday and Monday back to the previous Saturday. Tuesday and later will be left alone.

这篇关于VBA-日期减去从今天起的X天的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！