PDA

View Full Version : [SOLVED] Convert date to string yyyymmdd



Hambone
05-31-2013, 09:10 AM
Hello everyone: it has been years since i have touch vba and it appears what i knew back in the day no longer works in todays world lol... anyway here is what i am trying to do. I create new tabs everyday in an excel spreadsheet and name it ex. 20130531. i am trying to create a string that will allow me to call this page so i can gather data from it. i keep getting the value 5/31/2013 so it is not locating the tab. i have tried converting the date in the spreadsheet itself however it keeps giving me the wrong value as well.

Can anyone help?

Thanks
Tom

Hambone
05-31-2013, 09:30 AM
here are some of things i have tried


Dim dtmdate As Date
Dim strSheets As String
strSheets = Format(Now, "yyyymmdd")
dtmdate = Now() '.NumberFormat = "yyyymmdd"
Range("A1").Select
'Selection.NumberFormat = "yyyymmdd": ActiveCell.FormulaR1C1 = "=NOW()" ':
Selection.NumberFormat = "yyyymmdd"
'strSheets = Range("A1")
'dtmdate.numberformet = "yyyymmdd"
'Selection.NumberFormat = "yyyymmdd"
ActiveCell = dtmdate
strSheets = Range("A1")
Sheets("strSheets").Select

Kenneth Hobs
05-31-2013, 10:01 AM
We just discussed this recently.


strSheets = Format(Date, "yyyymmdd")

Hambone
05-31-2013, 10:14 AM
Kenneth thanks I get a compile error when I use Date just like when I use the Format. I must be missing some libraries in my VBA even though I can see them in my object browser.

Thanks
Tom

Hambone
05-31-2013, 10:46 AM
My issue is fixed Kenneth your suggestion worked.

Thanks
Tom

snb
06-01-2013, 03:44 AM
Some VBA alternatives:



Sub M_snb()
MsgBox DatePart("yyyy", Date) & Format(DatePart("m", Date), "00") & Format(DatePart("d", Date), "00")
MsgBox [text(today(),"yyyymmdd")]
MsgBox Application.Text(Date, "yyyymmdd")
End Sub