How to properly append an Excel variable at the end of URL address?

[Muir2014]

I have a VBA code using QueryTables, that have a URL as String--> exm: "http://homefold/exp/myFile.php" also have variable "hDate" as string having a date value. I need to add that variable at the end of the URL, seams that the task should be easy however I haven't found the solution. The result on sheet is something like : <b>Fatal error</b>: Function must be a string....... This is what I've tried so far : mURL="http://homefold/exp/myFile.php?hDate="& ws.Range("A1") I had switched the ampersand and the quotation in different positions but no success. Can't see what is wrong yet, need a solid on this one.

 

[6StringJazzer]

Is the value in A1 a Date value, or a string that represents a date?
What format does the web page expect the date parameter to be?

 

[Muir2014]

Is the value in A1 a Date value, or a string that represents a date?
What format does the web page expect the date parameter to be?

Thanks for the reply. The Excel variable "hDate" is dim as string, representing a date, exm "20220726". The format that web page expect is as a string.

 

[Rick Rothstein]

What is in cell A1... the real date July 26, 2022 formatted to display as 20220726 or a 20220726 in a cell formatted as Text or the real number 20220726?

 

[Muir2014]

Is the value in A1 a Date value, or a string that represents a date?
What format does the web page expect the date parameter to be?

This is the copy of code. The code runs without any compile errors or flags, but the result is : <b>Fatal error</b>: Function must be a string.......

Sub Imp_Query()
Dim mURL As String, hDate As String
Dim ws As Worksheet, ws2 As Worksheet
Dim Qtbl As QueryTable

Set ws = ThisWorkbook.Sheets("Sheet1")
Set ws2 = ThisWorkbook.Sheets("Sheet2")

hDate = ws2.Range("C1").Value
hDate = Format(hDate, "yyyymmdd")

mURL = "http://homefold/exp/myFile.php?hDate=" & hDate

Set Qtbl = ws.QueryTables.Add( _
Connection:="URL;" & mURL, _
Destination:=Range("B2"))

With Qtbl
.RefreshOnFileOpen = True
.Name = "MyFile"
.WebFormatting = xlWebFormattingRTF
.WebSelectionType = xlAllTables

.Refresh
End With

End Sub

 

[Muir2014]

What is in cell A1... the real date July 26, 2022 formatted to display as 20220726 or a 20220726 in a cell formatted as Text or the real number 20220726?

Thx Rick, the cell contains something like "07/26/2022", then I format this to "20220726" in the code :
hDate=ws2.range("A1").value
hDate=Format(hDate, "yyyymmdd")

 

[6StringJazzer]

You didn't answer my first question:

Is the value in A1 a Date value, or a string that represents a date?

What is the value of hDate after it is assigned the result of Format and before you assign mURL? If A1 contains a string then the result is going to be that string.

Also , this is not causing your error, but you have undeclared variables. hDate defaults to Variant and I recommend you do not use a Variant here, just as a good programming practice. I strongly recommend to everyone that they use Option Explicit and declare variables. Doing so prevents a lot of bugs and runtime errors.

Dim hDate As String
hDate = Format(ws2.Range("C1").Value, "yyyymmdd")

 

[Muir2014]

You didn't answer my first question:

Is the value in A1 a Date value, or a string that represents a date?

What is the value of hDate after it is assigned the result of Format and before you assign mURL? If A1 contains a string then the result is going to be that string.

Also , this is not causing your error, but you have undeclared variables. hDate defaults to Variant and I recommend you do not use a Variant here, just as a good programming practice. I strongly recommend to everyone that they use Option Explicit and declare variables. Doing so prevents a lot of bugs and runtime errors.

Dim hDate As String
hDate = Format(ws2.Range("C1").Value, "yyyymmdd")

The value in A1 is a date --> 07/26/2022 w/cell formatted as date. Then in the code I reformat as a string:

Dim hDate As String
hDate=ws.Range("A1").value
hDate=Format(hDate, "yyyymmdd")

One of the questions I have is if I have the right syntax when I wrote the URL reference --> mURL = "http://homefold/exp/myFile.php?hDate=" & hDate

 

[6StringJazzer]

Dim hDate As String
hDate=ws.Range("A1").value
hDate=Format(hDate, "yyyymmdd")

You can't do that. If you declare hDate as a String you can't treat it first as a date.

 

[Muir2014]

You can't do that. If you declare hDate as a String you can't treat it first as a date.

Then for that I a grabbed the value for an already format cell to general with value to 20220726, put it in variable string hDate, still the issue, which brings me the previous question, is that if I am paraphrasing the URL correctly, the URL supplied was "http://homefold/exp/MyFile.php" and I'm paraphrasing it like "http://homefold/exp/MyFile.php?hDate="& nDate.

Is that line syntax correct?