what is the wrong with my code?

[Al]

I am trying to hide a column in an excel file without having to make it
visible. the code below makes the whole work book hidden, I do not know why.
I, ultimately, need to be able to hide several columns, in any file that I
enter in a text box on a form and save the file.
thanks

Dim strPath As String, wsData As Worksheet, strFile As String
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWs As Excel.Worksheet
Dim xlRng As Excel.Range

strPath = Me.txtexcel
strFile = "hidc.xls"
Set xlApp = New Excel.Application
xlApp.Visible = True

Set xlWb = GetObject(strPath & strFile, "Excel.Sheet")
Set xlWs = xlWb.ActiveSheet
With xlWs
.Columns("C").Hidden = True
End With
xlWb.Close SaveChanges:=True

 

[BeWyched]

Hi

You don't need to create the Workbook as an VBA object. Also xlWS should be
an ActiveSheet of xlApp, not xlWb. Try:

strPath = Me.txtexcel
strFile = "hidc.xls"
Set xlApp = New Excel.Application
xlApp.Visible = True

Workbooks.Open (strPath & strFile)
Set xlWs = xlApp.ActiveSheet
With xlWs
.Columns("C").Hidden = True
End With

Cheers.

BW

 

[Al]

Hi BW,
thanks for your response. I modefied my previous code and this works:
Dim strPath As String, wsData As Worksheet, strFile As String, I As
Integer
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWs As Excel.Worksheet
Dim xlRng As Excel.Range

strPath = Me.txtexcel
strFile = "hidc.xls"
Set xlApp = New Excel.Application
xlApp.Visible = True

Set xlWb = xlApp.Workbooks.Open(strPath & strFile)
Set xlWs = xlWb.ActiveSheet

With xlWs
For I = 3 To 6
.Columns(I).Hidden = True
Next I
End With
xlWb.Close SaveChanges:=True
xlApp.Quit
*******************************************
I do not see how you would not declare the xlWb as a workbook. xlWs
(worksheet) is an object of the workbooks collection which, in turn, is an
object of the Application. to get to the Worksheet you must set the active
sheet as an object of the workbook. Without that, the code will cause
problems, which it did with me. did you try to run it?
My modification, however, does hide several columns now.
Al

 

[BeWyched]

Hi Al

Yup, I did run the code and it worked perfectly.

Why do you not need to declare the Workbook as an object? I guesse for the
same reason why, in Access you can refer to objects without the need of
creating a complete family tree of the underlying objects. We refer to, for
example, a forms control as 'me.control', without having to create an
Application, Form etc. object. Perhaps someone else can answer this!

Cheers and I'm pleased your code now works.

BW

 

[Al]

Hi BW,
Thank you again, you've got a point there. For whatever it is worth the
following line is what gave me the problem when I ran the code:

..Columns(I).Hidden = True

I got the following error message: "Object variable or with block variable
not set"
(Run Time Error 91)
Al