Previous Topic Next Topic

Concatonating within commands???

Solved/Closed
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 - Aug 8, 2015 at 09:37 PM
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 - Aug 21, 2015 at 01:18 PM
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?
Related:

3 responses

Answer 1 / 3
RayH
Aug 9, 2015 at 08:25 PM
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
0
Answer 2 / 3
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 150
Aug 19, 2015 at 04:37 PM
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.
0
Answer 3 / 3
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 150
Aug 21, 2015 at 01:13 PM
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.
0
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 150
Aug 21, 2015 at 01:18 PM
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!
0