Retrieving filename and changing format

[KezAng]

Hi. I'm very new to VBA and am trying to write some code that will open a file. I have the below information entered into a cell on an excel sheet:

Cell I5: <TABLE style="WIDTH: 336pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=448 x:str><COLGROUP><COL style="WIDTH: 336pt; mso-width-source: userset; mso-width-alt: 16384" width=448><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 336pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 width=448 x:str="S:\ftproot\download\ausbond\InteractiveDataComp\UBS Index Market Average ">S:\ftproot\download\ausbond\InteractiveDataComp\UBS Index Market Average </TD></TR></TBODY></TABLE>

In cells A2:A185 I have a list of dates:

A2: 27-Apr-11
A3: 28-Apr-11
etc

The full file name will be the contents of Cell I5 with a date from column A. So first file I want to open is: S:\ftproot\download\ausbond\InteractiveDataComp\UBS Index Market Average 270411.xls

The code I have written is:

Dim strFilename As String
strFilename = Range("I5").Cells.Value & Range("A2").Cells.Value

This isn't working as it is adding the date in A2 to the end of the filename in the wrong format ie. 27/04/2011

Does anyone know what code I can write that will change the format of cell A2 to be ddmmyy? I have tried changing the format in excel itself, but the VBA code still picks it up as 27/04/2011.

Thanks heaps.

 

[AlphaFrog]

Dim strFilename As String
strFilename = Range("I5").Value & Format(Range("A2").Value, " ddmmyy") & ".xls"

You may have to remove the leading space in " ddmmyy" depending on your name format.

 

[KezAng]

Thanks heaps AlphaFrog - works perfectly.
Sorry for taking so long to thank you!