How to run macros and make the workbooks invisible

[christine]

Tom, it works with or without the sreenupdating
statement.I want the workbooks to be invisible. I think
all i have to do is delete or modify something. I purpose
of this recorded code is to import about 5 text files.
Clean each files up and save it a excel web pages. I will
be using this same process very week. Instead of going
through the same process, i want to be able to run the
recorded marco.BUT I WANT THE WORKBOOKS(KEN, HECTOR, ETC)
TO BE INVISIBLE TO THE USER. Thanks for all your help. I
think was not making myslef clear. see codes. I think on
the next import text i should use the same workbooks but
how?

Sub test()
Application.ScreenUpdating = False
ChDir "C:\Documents and Settings\Desktop\Marco"
Workbooks.OpenText Filename:= _
"C:\Documents and
Settings\Desktop\Marco\Hector.tvr", Origin _
:=437, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, 9), _
Array(4, 1), Array(14, 1), Array(36, 1), Array
(58, 1)), TrailingMinusNumbers:=True
Rows("1:13").Select
Selection.Delete Shift:=xlUp
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D").EntireColumn.AutoFit
Columns("A").Select
Range("A57:B378").Select
Selection.ClearContents
Columns("A").Select
Selection.AutoFilter Field:=1, Criteria1:="Selected"
Selection.ClearContents
Columns("A").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="=*rank*",
Operator:=xlAnd
Range("A3:B324").Select
Selection.Font.ColorIndex = 2
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("C3:C324").Select
Selection.ClearContents
Selection.AutoFilter Field:=1
Columns("A").Select
ActiveSheet.ShowAllData
Selection.AutoFilter
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Date"
Range("A4325").Select
Selection.Font.ColorIndex = 2
With Selection.Interior
.ColorIndex = 41
.Pattern = xlSolid
End With
Selection.Font.Bold = True
Selection.AutoFilter Field:=1, Criteria1:="="
Selection.AutoFilter Field:=2, Criteria1:="="
Selection.AutoFilter Field:=3, Criteria1:="="
Selection.AutoFilter Field:=4, Criteria1:="="
Rows("5:376").Select
Selection.Delete Shift:=xlUp
ActiveSheet.ShowAllData
Columns("A").Select
Selection.AutoFilter
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Schedules"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Exception "
Range("D1").Select
ActiveCell.FormulaR1C1 = "Time"
Range("A11").Select
Selection.Font.ColorIndex = 2
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("A1256").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("B10").Select
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and
Settings\Desktop\Marco\Hector.htm", _
FileFormat:=xlHtml, ReadOnlyRecommended:=False,
CreateBackup:=False
Selection.Delete Shift:=xlToLeft
Workbooks.OpenText Filename:= _
"C:\Documents and
Settings\Desktop\Marco\Ken.tvr", Origin:= _
437, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, 9), _
Array(4, 1), Array(14, 1), Array(36, 1), Array
(57, 1), Array(64, 1)), _
TrailingMinusNumbers:=True
Rows("1:13").Select
Selection.Delete Shift:=xlUp
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D").EntireColumn.AutoFit
Columns("A:E").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="}"
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="="
Selection.AutoFilter Field:=2, Criteria1:="="
Selection.AutoFilter Field:=3, Criteria1:="="
Selection.AutoFilter Field:=4, Criteria1:="="
Selection.AutoFilter Field:=5, Criteria1:="="
Rows("2:460").Select
Range("A460").Activate
Selection.Delete Shift:=xlUp
ActiveSheet.ShowAllData
Selection.AutoFilter
Range("B406").Select
ActiveWindow.LargeScroll Down:=-10
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Columns("A:E").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Date"
Range("A3:E303").Select
Selection.Font.ColorIndex = 2
With Selection.Interior
.ColorIndex = 41
.Pattern = xlSolid
End With
Selection.Font.Bold = True
Selection.AutoFilter Field:=3
ActiveSheet.ShowAllData
Columns("A:E").Select
Selection.AutoFilter Field:=3, Criteria1:="=*rank*",
Operator:=xlAnd
Range("A2:B302").Select
Selection.Font.ColorIndex = 2
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("C2:C302").Select
Selection.ClearContents
Columns("A:E").Select
Selection.AutoFilter
Selection.AutoFilter
Selection.AutoFilter
Columns("C:C").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlEqual, _
Formula1:="=""vacation"""
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = True
.ColorIndex = 1
End With
Selection.FormatConditions(1).Interior.ColorIndex = 6
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlEqual, _
Formula1:="=""personal day off"""
With Selection.FormatConditions(2).Font
.Bold = True
.Italic = True
.ColorIndex = 2
End With
Selection.FormatConditions(2).Interior.ColorIndex = 9
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlEqual, _
Formula1:="=""e-time"""
With Selection.FormatConditions(3).Font
.Bold = True
.Italic = True
.ColorIndex = 2
End With
Selection.FormatConditions(3).Interior.ColorIndex = 10
Range("A1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Schedules"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Exception"
Range("D1").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlEqual, _
Formula1:="=""vacation"""
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = True
.ColorIndex = 1
End With
Selection.FormatConditions(1).Interior.ColorIndex = 6
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlEqual, _
Formula1:="=""personal day off"""
With Selection.FormatConditions(2).Font
.Bold = True
.Italic = True
.ColorIndex = 2
End With
Selection.FormatConditions(2).Interior.ColorIndex = 9
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlEqual, _
Formula1:="=""e-time"""
With Selection.FormatConditions(3).Font
.Bold = True
.Italic = True
.ColorIndex = 2
End With
Selection.FormatConditions(3).Interior.ColorIndex = 10
ActiveCell.FormulaR1C1 = "Start"
Range("E1").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlEqual, _
Formula1:="=""vacation"""
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = True
.ColorIndex = 1
End With
Selection.FormatConditions(1).Interior.ColorIndex = 6
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlEqual, _
Formula1:="=""personal day off"""
With Selection.FormatConditions(2).Font
.Bold = True
.Italic = True
.ColorIndex = 2
End With
Selection.FormatConditions(2).Interior.ColorIndex = 9
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlEqual, _
Formula1:="=""e-time"""
With Selection.FormatConditions(3).Font
.Bold = True
.Italic = True
.ColorIndex = 2
End With
Selection.FormatConditions(3).Interior.ColorIndex = 10
ActiveCell.FormulaR1C1 = "Stop"
Range("A1:E1").Select
Selection.Font.ColorIndex = 2
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("A1:E324").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("G19").Select
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and
Settings\Desktop\Marco\Ken.htm", FileFormat _
:=xlHtml, ReadOnlyRecommended:=False,
CreateBackup:=False
Application.ScreenUpdating = True
End Sub

 

[John Wilson]

christine,

You should stick with one thread and in one group.
Very confusing.
I don't believe that you can make a single workbook invisible
when you open it from a macro.
What you can do is make the entire application invisible until
your macro(s) are done.
Application.Visible = False.
Use Application.Visible = True at the end when
you've already closed the other workbooks.

John

 

[Tom Ogilvy]

I don't believe that you can make a single workbook invisible

when you open it from a macro.

Sub OpenWorkbookInvisibly()
Dim wkbk as Workbook
Dim sh as Worksheet
Application.ScreenUpdating = False
set wkbk = Workbooks.Open("C:\Test.xls")
for each sh in wkbk.Worksheets
sh.Activate
for each cell in sh.Range("A1:G15")
cell.Select
next
Next
wkbk.close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

Put a workbook named Test.xls in your root directory and run the above.
Ever see Test.xls?

 

[John Wilson]

Tom,

So I learn something new every day. <g>
This, of course, is dependent on the ScreenUpdating
setting "sticking" (mentioned in another thread).
Tried your code below and it worked perfectly.
I have a few workbooks where I've tried this and it doesn't
seem to work. I'll look at them tomorrow to see what else
might be resetting the ScreenUpdating.

Thanks,
John

 

[David McRitchie]

I don't know about the multiple workbooks part, but

It is generally not necessary to select something, and
you have a lot of selection going on. Example.

Rows("1:13").Select
Selection.Delete Shift:=xlUp
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D").EntireColumn.AutoFit
Columns("A").Select
Range("A57:B378").Select
Selection.ClearContents

I think can be reduced to
Rows("1:13").Delete Shift:=xlUp
Columns("A").EntireColumn.AutoFit
Range("A57:B378").ClearContents

The have a lot of formatting going on in your recorded
macro. Check that you need all of it, frequently things
get included that aren't really being changed.

try using something like

With Range("A3:B324")
.font.ColorIndex = 2
.interior.colorindex = 3
.interior.pattern = xlSolid
.Font.Bold = True
End With

instead of `

 

[Tom Ogilvy]

John does raise a good point

Try this:

Sub Test()
Application.Visible = False
' code without Application.ScreenUpdating


'
Application.Visible = True
End Sub

Perhaps that will be what you want.

 

[christine]

Thanks tom but john's statement is exactly what i was
looking for. thanks John.