macro flickering

**Issacraj** · 07-15-2012, 02:05 AM

hi all,

i am extracting a ASCII file to get a excel report... i have done it.... what i did is i recorded the macro...and did all column row adjusting in it...when i open my report it opens fine but since macro as recorded all the code to design the report...it flickers while loading...i want the report to freeze and load without any flickering any though in that.... do help.......thanks in advance

**JBeaucaire** · 07-15-2012, 02:54 AM

Add these line to your macro:

Sub MyMacro()
Application.ScreenUpdating = False

'your other action code goes here

Application.ScreenUpdating = True
End Sub

**Issacraj** · 07-15-2012, 03:37 AM

hi JBeaucaire,

thanks for the reply.. i used your code it works fine....the flickering has stopped....but after the full report is loaded the focus is on some 15 th or 14th record i have to scroll up to view the top....what should i do to return focus to a3 or a1,...

thanks in advance

**royUK** · 07-15-2012, 03:47 AM

The best way to stop the flickering is not to simply hide it.

Edit your code to remove selecting, activating, scrolling etc. That will also stop the active cell moving.

**Issacraj** · 07-15-2012, 03:54 AM

hi royUK, what you are saying is correct... i will try as u say thanks for ur reply

**JBeaucaire** · 07-15-2012, 04:04 AM

Post up more of your actual macro and we can help demonstrate ways to do as Roy has suggested.

**Issacraj** · 07-15-2012, 06:31 AM

hi here is my code...take a look and suggest me changes

Private Sub Auto_Open()
    Call WorktimeReport
End Sub

 Sub WorktimeReport()

Application.ScreenUpdating = False

    Sheets("Sheet1").Select
    Cells.Select
    Selection.ClearContents


    With ActiveSheet.QueryTables.Add(Connection:="TEXT;D:\transfer.Asc", _
        Destination:=Range("A4"))
        .Name = "transfer"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 720
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    Columns("M:AD").Select
    Selection.Delete Shift:=xlToLeft
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.SmallScroll Down:=-12
    Rows("3:3").Select
    Selection.Font.Bold = True
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "Date "
    Range("C3").Select
    ActiveCell.FormulaR1C1 = "Employee No"
    Columns("C:C").Select
    Selection.ColumnWidth = 11.57
    Range("D3").Select
    Columns("C:C").ColumnWidth = 12.29
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "Operation "
    Columns("E:E").Select
    Columns("D:D").ColumnWidth = 8.29
    Columns("D:D").ColumnWidth = 9
    Range("E3").Select
    ActiveCell.FormulaR1C1 = "Time"
    Columns("F:F").Select
    Selection.Delete Shift:=xlToLeft
    ActiveWindow.SmallScroll Down:=-18
    Range("H3").Select
    ActiveCell.FormulaR1C1 = "Job Order"
    Range("H3").Select
    Columns("H:H").ColumnWidth = 9.14
    ActiveWindow.SmallScroll Down:=-18
    Columns("I:J").Select
    Selection.Delete Shift:=xlToLeft
    Columns("F:G").Select
    Columns("F:G").EntireColumn.AutoFit
    ActiveWindow.SmallScroll Down:=15
    Selection.Delete Shift:=xlToLeft
    Columns("G:G").Select
    Selection.Delete Shift:=xlToLeft
    Range("G3").Select
    ActiveCell.FormulaR1C1 = "Regular"
    Range("H3").Select
    ActiveCell.FormulaR1C1 = "Overtime"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Soft Pro Daily Report"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = ""
    Range("D1").Select
    Columns("E:E").ColumnWidth = 17.14
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Soft Pro Daily Report"
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("E:E").ColumnWidth = 17.86
    Range("E3").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Soft Pro Daily Report"
    Rows("1:1").Select
    Selection.Font.Bold = True
    With Selection.Font
        .Name = "Arial"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Rows("1:1").Select
    With Selection.Font
        .Name = "Arial"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Range("G4").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(R[40440]C[-2]<=TIMEVALUE(""5:30:00 pm""),""1"",""0"")"
    Range("H4").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-3]>TIMEVALUE(""5:30:00 pm""),""1"",""0"")"
    Range("G4").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-2]<=TIMEVALUE(""5:30:00 pm""),""1"",""0"")"
    Range("H4").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-3]>TIMEVALUE(""5:30:00 pm""),""1"",""0"")"
    Range("G4").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-2]<=TIMEVALUE(""5:30:00 pm""),""1"",""0"")"
    Range("G2").Select
    Columns("G:G").ColumnWidth = 26.71
    Range("G4").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-2]<=TIMEVALUE(""5:30:00 pm""),""1"",""0"")"
    Range("G1").Select
    Columns("G:G").ColumnWidth = 34.29
    Range("G4").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-2]<=TIMEVALUE(""5:30:00 pm""),""1"",""0"")"
    Range("G1").Select
    Columns("G:G").ColumnWidth = 39.14
    Range("G4").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-2]<=TIMEVALUE(""5:30:00 pm""),""1"",""0"")"
    Selection.AutoFill Destination:=Range("G4:G45919")
    Range("G4:G45919").Select
    Range("G5").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-2]<=TIMEVALUE(""5:30:00 pm""),""1"",""0"")"
    Range("G8").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-2]<=TIMEVALUE(""5:30:00 pm""),""1"",""0"")"
    Range("G7").Select
    Columns("G:G").ColumnWidth = 9.57
    Columns("G:G").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("H4").Select
    Columns("H:H").ColumnWidth = 28
    Range("I1").Select
    Columns("H:H").ColumnWidth = 33.57
    Columns("H:H").ColumnWidth = 38.71
    Range("H4").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-3]>TIMEVALUE(""5:30:00 pm""),""1"",""0"")"
    Range("H4").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-3]>TIMEVALUE(""5:30:00 pm""),""1"",""0"")"
    Selection.AutoFill Destination:=Range("H4:H45919")
    Range("H4:H45919").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-3]>TIMEVALUE(""5:30:00 pm""),""1"",""0"")"
    Range("H3").Select
    ActiveCell.FormulaR1C1 = "Overtime"
    Range("H1").Select
    Columns("H:H").ColumnWidth = 13.57
    Columns("H:H").ColumnWidth = 10.29
    Range("H3").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 1
    Range("B3").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("E2").Select
   
    Application.ScreenUpdating = True
    Range("A1").Select
End Sub

thanks in advance

**Issacraj** · 07-15-2012, 06:47 AM

Also one more thing the ASCII file gets connected fine.. but i want the user to select whatever ASCII he likes not that one transfer ASCII.. i think it should be done with GET FILE function..but i dnt know how?... i want the pathname selected to be passed to the ActiveSheet.QueryTables.ADD(Connection:= like below

ActiveSheet.QueryTables.Add(Connection:="TEXT;D:\transfer.Asc", _
Destination:=Range("A4"))

any ideas guys...thanks in advance

**Cutter** · 07-15-2012, 08:36 AM

Hello Issacraj, and welcome to the forum. Please notice that I have added code tags to your posts. The forum rules require that you use them when posting code so please keep that in mind for your future posts. Thanks.

Here is the rule:

RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**royUK** · 07-15-2012, 09:14 AM

Why don't you just create a template for this

You could have cleaned up a lot of your code & learned what it is doing in the process,e.g. you select cells several times & change widths, etc . The recorder catches you changing your mind but you don't need the extra code.

Do you need to fill in all those formulas?



Sub WorktimeReport()


    With Sheets("Sheet1")
        .UsedRange.ClearContents

        With Sheets("Sheet1").QueryTables.Add(Connection:="TEXT;D:\transfer.Asc", _
                                              Destination:=Range("A4"))
            .Name = "transfer"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 720
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = True
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = True
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = True
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
                                             1, 1, 1, 1, 1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With

        .Columns("M:AD").Delete Shift:=xlToLeft
        .Rows("3:3").Font.Bold = True
        .Range("B3").Value = "Date "
        .Range("C3").Value = "Employee No"
        .Columns("C:C").ColumnWidth = 11.57
        .Columns("D:D").ColumnWidth = 12.29
        .Range("D3").vale = "Operation "
        .Columns("D:D").ColumnWidth = 9
        .Range("E3").Value = "Time"
        .Columns("F:F").Delete Shift:=xlToLeft
        .Range("H3").Value = "Job Order"
        .Columns("H:H").ColumnWidth = 9.14
        .Columns("I:J").Delete Shift:=xlToLeft
        .Columns("F:G").Delete Shift:=xlToLeft
        .Columns("G:G").Delete Shift:=xlToLeft
        .Range("G3").Value = "Regular"
        .Range("H3").Value = "Overtime"
        .Range("E1").Value = "Soft Pro Daily Report"
        .Range("G1").ClearContents
        .Columns("E:E").ColumnWidth = 17.14

        With .Range("E1")
            .Value = "Soft Pro Daily Report"
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        .Columns("E:E").ColumnWidth = 17.86

        With .Range("E3")
            .HorizontalAlignment = xlRight
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        .Range("E1").Value = "Soft Pro Daily Report"


        With Rows("1:1").Font
            .Bold = True
            .Name = "Arial"
            .Size = 14
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = xlAutomatic
        End With

        .Range("G4").FormulaR1C1 = _
        "=IF(R[40440]C[-2]<=TIMEVALUE(""5:30:00 pm""),""1"",""0"")"
        .Range("H4").FormulaR1C1 = "=IF(RC[-3]>TIMEVALUE(""5:30:00 pm""),""1"",""0"")"
        .Range("G4").FormulaR1C1 = "=IF(RC[-2]<=TIMEVALUE(""5:30:00 pm""),""1"",""0"")"
        .Range("H4").FormulaR1C1 = "=IF(RC[-3]>TIMEVALUE(""5:30:00 pm""),""1"",""0"")"
        .Range("G4").FormulaR1C1 = "=IF(RC[-2]<=TIMEVALUE(""5:30:00 pm""),""1"",""0"")"
        .Columns("G:G").ColumnWidth = 26.71
        .Range("G4").FormulaR1C1 = "=IF(RC[-2]<=TIMEVALUE(""5:30:00 pm""),""1"",""0"")"
        .Columns("G:G").ColumnWidth = 34.29
        .Range("G4").FormulaR1C1 = "=IF(RC[-2]<=TIMEVALUE(""5:30:00 pm""),""1"",""0"")"
        .Columns("G:G").ColumnWidth = 39.14
        .Range("G4").FormulaR1C1 = "=IF(RC[-2]<=TIMEVALUE(""5:30:00 pm""),""1"",""0"")"
        .Range("G4").AutoFill Destination:=.Range("G4:G45919")
        .Range("G5").FormulaR1C1 = "=IF(RC[-2]<=TIMEVALUE(""5:30:00 pm""),""1"",""0"")"
        .Range("G8").FormulaR1C1 = "=IF(RC[-2]<=TIMEVALUE(""5:30:00 pm""),""1"",""0"")"

        With Columns("G:G")
            .ColumnWidth = 9.57
            .HorizontalAlignment = xlRight
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        .Columns("H:H").ColumnWidth = 28
        .Columns("H:H").ColumnWidth = 38.71
        .Range("H4").FormulaR1C1 = "=IF(RC[-3]>TIMEVALUE(""5:30:00 pm""),""1"",""0"")"
        .Range("H4").FormulaR1C1 = "=IF(RC[-3]>TIMEVALUE(""5:30:00 pm""),""1"",""0"")"
        .Range("H4").AutoFill Destination:=.Range("H4:H45919")
        .Range("H3").FormulaR1C1 = "Overtime"
        .Columns("H:H").ColumnWidth = 10.29

        With .Range("H3")
            .HorizontalAlignment = xlRight
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With

        With .Range("B3")
            .HorizontalAlignment = xlRight
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With

    End With


End Sub

**Issacraj** · 07-15-2012, 10:06 AM

@cutter... will do hereafter...

@roy thanks for the code... it was wonderful...

in one more thing i need your help.. in the code we are loading transfer.asc statically but want to capture and pass the pathname to an argument as string and then pass it to the connection argument below

 With Sheets("Sheet1").QueryTables.Add(Connection:="TEXT;D:\transfer.Asc", _
                                              Destination:=Range("A4"))

so that the user may be able select data from any asc file not neccessarily from transfer.ascthat is he can dynamically select an asc file... any ideas

thanks in advance

**JBeaucaire** · 07-15-2012, 05:58 PM

Add a string variable to store the file you select, then feed that into the ADD.

Sub WorktimeReport()
Dim fNAME As String

fNAME = Application.GetOpenFilename("ASCII Files (*.asc),*.asc")
If fNAME = "False" Then Exit Sub
    With Sheets("Sheet1")
        .UsedRange.ClearContents

        With Sheets("Sheet1").QueryTables.Add(Connection:="TEXT;" & fNAME, _
                                              Destination:=Range("A4"))
            .Name = "transfer"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
'etc...

If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.

**Issacraj** · 07-16-2012, 01:05 AM

hi jerry, thanks for the code...it works perfectly and have solved my problem...thanks for the help

macro flickering

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

macro flickering

Re: macro flickering

Re: macro flickering

Re: macro flickering

Re: macro flickering

Re: macro flickering

Re: macro flickering

Re: macro flickering

Re: macro flickering

Re: macro flickering

Re: macro flickering

Re: macro flickering

Re: macro flickering

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions