Hiding two columns
 

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.

Hiding two columns
 

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.


CAM wrote:

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

Hiding two columns
 

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

Hiding two columns
 

Thanks Dave,

Works like a charm. I appreciate your help.
cheers

"Dave Peterson" wrote in message
...
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.


CAM wrote:

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