Previous Topic Next Topic

Concatonating within commands???

[Solved/Closed]
Report
Posts
1010
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
September 30, 2021
 -
Posts
1010
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
September 30, 2021
 -
Ok - I know I said no more scripting, but ...

Im still with Excel (VBA) macros and trying to find a way of inputting data from a html sheet (stored on the local machine) into the Excel sheet for manipulation of the stuff in the html. The problem is I need to access the latest file which is a product of the date (in yymmdd format) and some text . What I need to do is find a way of telling Excel which file to pull.

an example of what I have and works (but I have to change the macro every time I run it to get the file date right) is here ... 

 Sub test()

Sheets("my data").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\myFiles\150805_final.html", Destination:=Range(
        "$A$1"))
        .Name = "150805_final_1"
End sub


and what I want is (sort of) here ... 

Sub test()

theDate = DateSerial(Year(Now), Month(Now), Day(Now) - 1)
theDate1 = Format$(theDate, "yymmdd")
 
Sheets("my data").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\myFiles\" & "theDate1" & "_final.html", Destination:=Range(
        "$A$1"))
        .Name = "theDate" & "_final_1"

End sub


Sadly this doesnt work at all - Excel tells me Im stupid before I get to run thew macro.

Any ideas please - I will be forever in your debt ... or did I say that last time?

3 replies

Answer 1 / 3

Hi Brian,
cant exactly emulate what you want but the string formation is wrong:
try this instead:

Sheets("my data").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\myFiles\" & theDate1 & "_final.txt", Destination:=Range("$A$1"))
.Name = "theDate" & "_final_1"
End With
Answer 2 / 3
Posts
1010
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
September 30, 2021
 143
Thank you Ray.

Appologies for not getting back sooner, but holidays got in the way of work and a myriad of other joyous things.

At first glance your script seems to work, but there are a few other problems. I think this is because I have forgotten to add a thing in another part of my script which copies cells from one sheet to another. This would tells me things are working as I want them to or not. However, your addition does seem to let me past the initial failings and faults I was experiencing.

I will hopefully be able to mark this as SOLVED later today (but more likely tomorrow) when I have trawled through the rest of the script to see that everything else does work :^)


Thanks again for your help - it is very much appreciated.
Answer 3 / 3
Posts
1010
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
September 30, 2021
 143
Yipeeeeee

All the failings have been resolved. They were all to do with my part of the script (would you ever believe it?)

Thank you so much Ray - as ever you are a star.
Report
Posts
1010
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
September 30, 2021
 143
oh - just one other report to make ... to get everything to work ok i had to modify your .name line to:

.Name = theDate & "_final_1"

But without your initial help I would nrever have got there!
Ask a question