Help - macro for importing csv file into Excel and format

[yorkshirelad]

This would get me out of a jam if someone could help.

I have csv file dailyrecords.txt in a folder 'records' on my desktop, which is replaced with a new file every day.

Once the file is replaced I need to import this into Excel and format on a daily basis.

Is there a macro I could set in Excel that would allow me to

a) Navigate to open the file, identify it as comma deliminated fiile, delete columns e and f and then import it into Excel.

b) After the import is completed I then need to set columns C & D to number format and set each column to auto-stretch.

c) I'd then like it to then prompt me to save it in the original location as Excel 97-2003 file called drecords_todaysdate.xls?

Any help would be really appreciated - please come back to me with any questions.

 

[cornflakegirl]

Have you tried recording a macro while you complete those steps?

 

[yorkshirelad]

Doh just realised what I was doing, I've opened it as a text file and its worked, I was getting bogged down trying to record importing a csv and setting the columns.

Thanks for your feedback. The only thing I need is to know is how to save the file automatically as drrecords but include today's date in the file name.

Many thanks

 

[Ruddles]

Try this as a skeleton:-

Option Explicit
 
Public Sub Import()
 
  Dim sInFile As String
  Dim sOutFile As String
 
  sInFile = Application.GetOpenFilename(FileFilter:="Text files (*.txt), *.txt")
  If sInFile = "False" Then Exit Sub
 
  sOutFile = "drecords_" & Format(Now(), "[COLOR=red][B]yyyymmdd[/B][/COLOR]")
  sOutFile = Application.GetSaveAsFilename(InitialFileName:=sOutFile, FileFilter:="Excel 97-2003 files (*.xls), *.xls")
  If sOutFile = "False" Then Exit Sub
 
[COLOR=green] ' code to import the text file goes here[/COLOR]
  
  Columns("E:F").Delete Shift:=xlToLeft
  Columns("C:D").NumberFormat = "[COLOR=red][B]0.00[/B][/COLOR]"
  Cells.EntireColumn.AutoFit
 
  Application.DisplayAlerts = False
  ThisWorkbook.SaveAs sOutFile, xlExcel8
  Application.DisplayAlerts = True
 
End Sub

All you have to do is supply the import code as this is specific to the format of your CSV file. You may also want to adjust the bits in red.

 

[Ruddles]

You could also precede the .GetOpenFilename and .GetSaveAsFilename statements with commands to switch to the drive and folder you want to start the navigating from:-

  ChDrive "C:"
  ChDir "\Temp"

(for example).

Note that my code in the previous post relies on you starting with a workbook with just my code in it and an empty Sheet1. When you run the code, the workbook is saved with the 'drecords_date' filename and the original workbook with the code in it is effectively closed.