Option Explicit
Sub OpenConvertSave()
'JBeaucaire (8/12/2009)
'Opens ALL CSV files in a folder, manipulates then saves as Excel
'Moves imported files into an IMPORTED folder in the same directory
Dim fName As String, OldDir As String, Cnt As Long
Dim fSave As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'memorizes the users current working path
OldDir = CurDir
'Path with the files to convert, create the imported folder if needed
ChDir "D:\2010\"
On Error Resume Next
MkDir CurDir & "\Imported\"
On Error GoTo 0
'Create a list of the files in that folder
fName = Dir("*.csv")
'Open files one at a time to process them
Do While Len(fName) > 0
'Open file
Workbooks.Open fName
'your code here
Call JusteraRutter
'Save to same directory as Excel file with same name
fSave = Trim(Left(fName, InStr(fName, "[") - 1))
ActiveWorkbook.SaveAs Filename:=fSave, FileFormat:=xlNormal, CreateBackup:=False
ActiveWorkbook.Close False
Name fName As "Imported\" & fName
Cnt = Cnt + 1
'Get next filename
fName = Dir
Loop
MsgBox "Complete, " & Cnt & " files were processed"
ChDir OldDir 'restores users original working path
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Sub JusteraRutter()
Dim LR As Long, LC As Long
'
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1)), TrailingMinusNumbers:=True
Range("D1").Select
Selection.End(xlDown).Select
Selection.Copy
ActiveCell.Offset(1, -1).Activate
ActiveSheet.Paste
Range("D3").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Range("C2").Select
Selection.End(xlDown).Select
Selection.Cut
ActiveCell.Offset(-1, 1).Activate
ActiveSheet.Paste
Columns("H:M").Insert Shift:=xlToRight
LR = Range("G" & Rows.Count).End(xlUp).Row
Range("H2:H" & LR).FormulaR1C1 = _
"=IF(ISERROR(FIND(""PLD"",RC[-2],1)),"""",MID(RC[-2],FIND(""PLD"",RC[-2],1)+4,5)*1)"
Range("I2:I" & LR).FormulaR1C1 = _
"=IF(ISERROR(FIND(""PL Ej Direkt"",RC[-3],1)),"""",MID(RC[-3],FIND(""PL Ej Direkt"",RC[-3],1)+13,5)*1)"
Range("J2:J" & LR).FormulaR1C1 = _
"=IF(ISERROR(FIND(""FBX"",RC[-4],1)),"""",MID(RC[-4],FIND(""FBX"",RC[-4],1)+4,5)*1)"
Range("K2:K" & LR).FormulaR1C1 = _
"=IF(ISERROR(FIND(""FFH Gång"",RC[-5],1)),"""",MID(RC[-5],FIND(""FFH Gång"",RC[-5],1)+9,5)*1)"
Range("L2:L" & LR).FormulaR1C1 = _
"=IF(ISERROR(FIND(""FFH Hiss"",RC[-6],1)),"""",MID(RC[-6],FIND(""FFH Hiss"",RC[-6],1)+9,5)*1)"
Range("M2:M" & LR).FormulaR1C1 = _
"=IF(ISERROR(FIND(""Fritidshushåll"",RC[-7],1)),"""",MID(RC[-7],FIND(""Fritidshushåll"",RC[-7],1)+15,5)*1)"
Range("H1") = " PLD "
Range("I1") = " PL-Ej Dir "
Range("J1") = " FBX "
Range("K1") = " FFH-G "
Range("L1") = " FFH-H "
Range("M1") = " SPL "
Rows("1:1").Insert Shift:=xlDown
LC = Cells(2, Columns.Count).End(xlToLeft).Column
With Range("A1", Cells(2, LC))
.Font.Bold = True
.Interior.ColorIndex = 15
End With
Range("D1:E1").HorizontalAlignment = xlRight
Range("E1") = "Antal stopp med:"
Range("H1:M1").FormulaR1C1 = "=COUNT(R[2]C:R[500]C)"
Range("A:A,C:E,G:G,H:N").HorizontalAlignment = xlCenter
Columns("G:M").Value = Columns("G:M").Value
Columns("F:F").Copy Range("P1")
Columns("F:F").HorizontalAlignment = xlCenter
Columns("F:F").Select
Selection.ClearContents
Range("F4").FormulaR1C1 = "=RC[-1]-R[-1]C[-1]"
Range("F4").Select
Selection.Copy
Range("E2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Activate
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Range("F4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="5"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Strikethrough = False
.ColorIndex = 3
End With
Range("F2") = "Avstånd"
Cells.Select
Cells.EntireColumn.AutoFit
Columns("F").Value = Columns("F").Value
Range("A1").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$2"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$A:$M"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.393700787401575)
.RightMargin = Application.InchesToPoints(0.393700787401575)
.TopMargin = Application.InchesToPoints(0.590551181102362)
.BottomMargin = Application.InchesToPoints(0.393700787401575)
.HeaderMargin = Application.InchesToPoints(0.511811023622047)
.FooterMargin = Application.InchesToPoints(0.511811023622047)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
End Sub
Bookmarks