Hiding two columns

[CAM]

Hello,

I am using Excel 2007 and I have a folder called "c:\Test" in this folder I
have about 25 Excel files that I want to hide only two columns - column K
and column L. All of the 25 files are the same. I also if possible like to
have the coding using a command button in a separate Excel workbook. How
do I do this? Any help will be appreicated. thank you in advance.

 

[Dave Peterson]

Untested, but it did compile:

Option Explicit
Sub testme01()

Dim myNames() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim wks As Worksheet
Dim TempWkbk As Workbook

'change to point at the folder to check
myPath = "C:\test"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

myFile = Dir(myPath & "*.xls")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile <> ""
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = myFile
myFile = Dir()
Loop

If fCtr > 0 Then
For fCtr = LBound(myNames) To UBound(myNames)
Set TempWkbk = Workbooks.Open(Filename:=myPath & myNames(fCtr))

For Each wks In TempWkbk.Worksheets
wks.Range("K:L").EntireColumn.Hidden = True
Next wks

TempWkbk.Close savechanges:=True
Next fCtr

End If

End Sub

If there was only one worksheet in each workbook that needed to hide K:L (and
you knew the name), you could use:

TempWkbk.Worksheets("SomeSheetnameHere") _
.Range("K:L").EntireColumn.Hidden = True

instead of:

For Each wks In TempWkbk.Worksheets
wks.Range("K:L").EntireColumn.Hidden = True
Next wks


I'd add a button from the forms toolbar--not a command button and then assign
this macro to that button.

 

[marcus]

Hi Cam

I answered a similar question for you last week.

This code does what you want and you can put it in any workbook to run
it from.

Good luck with this.

Marcus

Sub OpenFiles()

Dim wkbk As Workbook
Dim Lw As Integer, Sr As Integer

With Application.FileSearch
.NewSearch
.LookIn = "c:\Test"
.SearchSubFolders = False
.Filename = ".xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Set wkbk = Workbooks.Open(.FoundFiles(i))
Columns("K:L").Insert
wkbk.Close SaveChanges:=True
Next i

End If
End With
End Sub

 

[CAM]

Thanks Dave,

Works like a charm. I appreciate your help.
cheers