Results 1 to 10 of 10

Macro not working correctly, what did I do wrong?

Threaded View

  1. #1
    Registered User
    Join Date
    03-08-2010
    Location
    Sweden
    MS-Off Ver
    MS Office 365 Mac and Windows
    Posts
    94

    Macro not working correctly, what did I do wrong?

    This is the code I am using, the problem is that it does not process the file it gives me "Complete 0 files where processed" It seems not calling the other function, but when I change its the name it ask for it and when I change the path for the files it gives me "could not find the path". When only running the "call subfunction" it works fine but together with Sub "OpenConvertSave" it does nothing... something I am missing

    Sorry for the trouble...

    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
    Last edited by soreno; 03-12-2010 at 03:32 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1