Get latest CSV and retain formatting

[RoganLogan]

Hi Guys,<o></o>
<o></o>
I’m trying to open the latest .csv file from a folder using vba andthen to copy and paste some of the columns from the csv into a spreadsheet thatI have. The code that I ams using worksfine to open the lastest csv but once it copies ans pastes the data formatting changeson some the columns from date to general which stops the formulas I have inspreadsheet from working.I have triedchanging the format before I copy and paste as in the code below but for somereason it is still not working.I guessit has something to do with the file being csv?The code I have is below.<o></o>
<o></o>

<o:p></o:p>
Sub OpenLatestFile()<o:p></o:p>

Dim MyPath As String<o:p></o:p>
Dim MyFile As String<o:p></o:p>
Dim LatestFile As String<o:p></o:p>
Dim LatestDate As Date<o:p></o:p>
Dim LMD As Date<o:p></o:p>
    <o:p></o:p>
MyPath = "D:\Folers"<o:p></o:p>
    <o:p></o:p>
If Right(MyPath, 1) <>"\" Then MyPath = MyPath & "\"<o:p></o:p>
    <o:p></o:p>
    <o:p></o:p>
MyFile = Dir(MyPath &"*.csv", vbNormal)<o:p></o:p>
    <o:p></o:p>
If Len(MyFile) = 0 Then<o:p></o:p>
MsgBox "No fileswere found...", vbExclamation<o:p></o:p>
Exit Sub<o:p></o:p>
End If<o:p></o:p>
    <o:p></o:p>
Do While Len(MyFile) > 0<o:p></o:p>
    <o:p></o:p>
LMD = FileDateTime(MyPath& MyFile)<o:p></o:p>
        <o:p></o:p>
If LMD > LatestDateThen<o:p></o:p>
LatestFile = MyFile<o:p></o:p>
LatestDate = LMD<o:p></o:p>
End If<o:p></o:p>
       <o:p></o:p>
MyFile = Dir<o:p></o:p>
        <o:p></o:p>
Loop<o:p></o:p>
    <o:p></o:p>
Workbooks.Open MyPath &LatestFile<o:p></o:p>
    <o:p></o:p>
   <o:p></o:p>
Columns("A:D").Select<o:p></o:p>
Selection.NumberFormat ="dd/mm/yyyy"<o:p></o:p>
   <o:p></o:p>
Columns("A:AB").Select<o:p></o:p>
Selection.Copy<o:p></o:p>
Workbooks("Workbook1").Activate<o:p></o:p>
Worksheets("Sheet1").Activate<o:p></o:p>
ActiveSheet.Range("A1").PasteSpecialPaste:=xlPasteValuesAndNumberFormats, _<o:p></o:p>
Operation:=xlNone, SkipBlanks:=False, Transpose:=False<o:p></o:p>
   <o:p></o:p>
        <o:p></o:p>
End Sub<o:p></o:p>
<o:p></o:p>

<o></o>
<o></o>
Any help or assistance is greatly appreciated.<o></o>

 

[John_w]

if you import the csv file using the Text Import Wizard (Data menu -> From Text) then you can choose the format of each column and skip columns. Record a macro whilst doing a manual import to generate the VBA code, which you would need to modify (e.g. to use the latest csv file instead of a specific csv file) and incorporate into your code.

 

[RoganLogan]

Hi John,

Thanks for getting back to me. Managed to figure it out with your help. Cheers.