[RESOLVED] Select Method of Range Class failed Error?

[blakemckenna]

I keep getting the above error message on the following line of code:

      myXLWrkSheet.Columns("A:A").Select

Here is the rest of my code so it makes more sense.

            If aHit > 0 Then
                Set myXLWrkBook = GetObject(f2)

                Set myXLWrkSheet = myXLWrkBook.Worksheets(1)
                numCols = myXLWrkSheet.UsedRange.Columns.count
                numRows = myXLWrkSheet.UsedRange.rows.count
                strCol = "A" & numCols
                
                myXLWrkSheet.Columns("A:V").Select  ////// Error Msg Occurs here  \\\\\\\
                myXLWrkSheet.Columns("A:V").EntireColumn.AutoFit
                myXLWrkSheet.Rows("1:1").Select
            
                With myXLWrkSheet
                    .Name = "MS Sans Serif"
                    .Size = 8.5
                End With

                myXLWrkSheet.Selection.Font.Bold = True
                myXLWrkSheet.Columns("A:A").Select
                myXLWrkSheet.Selection.Insert Shift:=xlToRight
                myXLWrkSheet.Range("A2").Select
                myXLWrkSheet.ActiveCell.FormulaR1C1 = "=HYPERLINK(RC[14],RC[1])"
                myXLWrkSheet.Selection.Copy
                myXLWrkSheet.Range("B2").Select
                myXLWrkSheet.Selection.End(xlDown).Select
                myXLWrkSheet.Selection.End(xlToLeft).Select
                myXLWrkSheet.Range("A3:A4").Select
                myXLWrkSheet.Range("A4").Activate
                myXLWrkSheet.ActiveSheet.Paste
            Else
                myXLWrkSheet.Columns("A:V").Select
                myXLWrkSheet.Columns("A:V").EntireColumn.AutoFit
                myXLWrkSheet.rows("1:1").Select
            
                With myXLWrkSheet
                    .Name = "MS Sans Serif"
                    .Size = 8.5
                End With
                
                myXLWrkSheet.Range("A" & startrow & ":A" & stoprow).Copy
                myXLWrkSheet.Columns("A:V").Select
                myXLWrkSheet.Columns("A:V").EntireColumn.AutoFit
                myXLWrkSheet.rows("1:1").Select
                myXLWrkSheet.Selection.Font.Bold = True
            End If

[blakemckenna]

Does anyone have any ideas???? I really need to get this code out by end-of-day.

Thanks,

Blake

[RobDog888]

Could there be something to do with your specific sheet since it works.

Also, is that sheet the active sheet? Try selecting the sheet first then make your selection.

Sheets(1).Select
    Sheets(1).Columns("A:V").Select

[blakemckenna]

Rob,

I'll give that a try...

Thanks,

Blake

[blakemckenna]

Rob,

It's not liking any of this code. What am I doing wrong?

Blake

[RobDog888]

Test it in a new workbook. If it works in the new one then you must have applied something to it that it doesnt like.

[blakemckenna]

Rob,

I tried using this code from within a new Access DB. The code accessed the same XLS file and still gave me the same error. Should I try a completely blank worksheet?

[RobDog888]

Yes, and is the workbook visible when you were testing?

[blakemckenna]

No....the workbook is not visible...how do I do that?

[RobDog888]

I'm 99.9% sure that it needs to be visible in order to make any kind of selection.
You can do an ..

myXLApp.Visible = True

[blakemckenna]

I figured that one out Rob but it still didn't work. I don't understand...this is really weird!!!

[RobDog888]

Does it work in a new workbook? Could it be your installation if it doesnt work in a new visible one.

[blakemckenna]

Rob,

It doesn't work in a new workbook. I can I email you Function. I have certain lines highlighted with colors that might explain things better.

Thanks,

Blake

[Webtest]

Maybe the sheet isn't active? Try: myXLWrkSheet.Activate before selecting the column.

[Webtest]

I'm fairly certain that is the answer ... there is no provision in your code to assure that the sheet is active, and it must be the active sheet to perform selections on it. The selected area does not need to be visible! I learned that in the "School of Hard Knocks"!

[blakemckenna]

Could you please show me the code necessary to acheive this?

Thanks,

Blake

[RobDog888]

Maybe the sheet isn't active? Try: myXLWrkSheet.Activate before selecting the column.

Doh! That should take care of the selection issue. I was thinking Visible when I should have thought Active.
Also, I got this for the autofit line - myXLWrkSheet.Columns("A:V").AutoFit

[blakemckenna]

I'll give it a try....

thanks!

[blakemckenna]

Still didn't work...

Here's the revised code:

    If fso.FolderExists(filepath) Then
        Set f = fso.GetFolder(filepath)
        Set fc = f.SubFolders
        Set fj = f.Files

        For Each f2 In fj
            Set myXLWrkBook = GetObject(f2)
            Set myXLWrkSheet = myXLWrkBook.Worksheets(1)
            myXLWrkSheet.Activate     /////////// Still not working with this line of code \\\\\\\\\\\\
            
            With myXLWrkSheet
                With .Rows("1:1").Font
                    .Name = "MS Sans Serif"
                    .Bold = True
                    .Size = 8.5
                End With
            End With
            
            aHit = InStr(f2, "DETAIL")
            
            If aHit > 0 Then
                numCols = myXLWrkSheet.UsedRange.Columns.count
                strCol = "A" & numCols
                
'                myXLWrkSheet.active = True
                'myXLWrkSheet.Columns("A:V").Select
                myXLWrkSheet.Columns("A:V").EntireColumn.AutoFit
                'myXLWrkSheet.Rows("1:1").Select
                
                
                myXLWrkSheet.Selection.Font.Bold = True
                myXLWrkSheet.Columns("A:A").Select
                myXLWrkSheet.Selection.Insert Shift:=xlToRight
                myXLWrkSheet.Range("A2").Select
                myXLWrkSheet.ActiveCell.FormulaR1C1 = "=HYPERLINK(RC[14],RC[1])"
                myXLWrkSheet.Selection.Copy
                myXLWrkSheet.Range("B2").Select
                myXLWrkSheet.Selection.end(xlDown).Select
                myXLWrkSheet.Selection.end(xlToLeft).Select
                myXLWrkSheet.Range("A13:A14").Select
                myXLWrkSheet.Range("A14").Activate
                myXLWrkSheet.Range(Selection, Selection.end(xlUp)).Select
                myXLWrkSheet.Range("A2:A14").Select
                myXLWrkSheet.Range("A14").Activate
                myXLWrkSheet.ActiveSheet.Paste
            End If

            myXLWrkBook.Save
            myXLWrkBook.Close
            Set myXLWrkSheet = Nothing
            Set myXLWrkBook = Nothing
            aHit = 0
        Next
    End If

[RobDog888]

'It should be ...
myXLWrkSheet.Activate

[blakemckenna]

I tried that Rob and it still doesn't work. I still get the same error messages. I don't know what to do...

[RobDog888]

I think it may have something to do with the way your instanciating the object.

Set myXLWrkBook = GetObject(f2)
'Try instead
Set myXLApp = CreateObject("Excel.Application")
Set myXLWrkBook = myXLApp.Workbooks.Open(f2)
'...
'...

[Webtest]

For heaven's sake, put "Option Strict" as the very top line of your module. If it breaks anything, that is GOOD, because broken things were being hidden!

[blakemckenna]

I get an "Expected: Base or Compare or Explicit or Private" error when I try to insert "Option Strict".

Once again...here's what I have so far....

        For Each f2 In fj
            Set myXLApp = CreateObject("Excel.Application")
            Set myXLWrkBook = myXLApp.Workbooks.Open(f2)
            
            Set myXLWrkSheet = myXLWrkBook.Worksheets(1)
            myXLWrkBook.Application.Windows(1).Visible = True
            
            With myXLWrkSheet
                With .Rows("1:1").Font
                    .Name = "MS Sans Serif"
                    .Bold = True
                    .Size = 8.5
                End With
            End With
            
            aHit = InStr(f2, "DETAIL")
            
            If aHit > 0 Then
                numCols = myXLWrkSheet.UsedRange.Columns.count
                strCol = "A" & numCols
                
                myXLWrkSheet.Columns("A:V").EntireColumn.AutoFit
                myXLWrkSheet.Columns("A:V").Select
                myXLWrkSheet.Rows("1:1").Select
                
'                myXLWrkSheet.Selection.Font.Bold = True
                myXLWrkSheet.Columns("A:A").Select
'                myXLApp.myXLWrkSheet.Selection.Insert Shift:=xlToRight
                myXLWrkSheet.Range("A2").Select
'                myXLWrkSheet.ActiveCell.FormulaR1C1 = "=HYPERLINK(RC[14],RC[1])"
'                myXLWrkSheet.Selection.Copy
                myXLWrkSheet.Range("B2").Select
'                myXLWrkSheet.Selection.End(xlDown).Select
'                myXLWrkSheet.Selection.End(xlToLeft).Select
                myXLWrkSheet.Range("A13:A14").Select
                myXLWrkSheet.Range("A14").Activate
'                myXLWrkSheet.Range(Selection, Selection.End(xlUp)).Select
                myXLWrkSheet.Range("A2:A14").Select
                myXLWrkSheet.Range("A14").Activate
'                myXLWrkSheet.ActiveSheet.Paste
            End If
            
            myXLWrkBook.Save
            myXLWrkBook.Close
            Set myXLWrkSheet = Nothing
            Set myXLWrkBook = Nothing
        Next

The only thing not working now are the lines of code that are commented out.

I am getting closer!!!!!

Thanks to all who are helping!

[RobDog888]

You can not use "Option Strict" in VB6/VBA. Its only for .NET
You may be thinking of "Option Explicit" for VB6/VBA.

So it was the GetObject that was causing some of the issues.

[blakemckenna]

Who knows....all I know is that it didn't fix it completely. I just don't know VBA well enough to figure this out on my own.

I'll stick with straight VB anyday....HAHA

[RobDog888]

Here's the fix...

Selection.Font.Bold = True
'Repeat syntax for other .Selection lines of code

The Selection object is an Application level object and not a sheet level object.

[Webtest]

Regarding the "Option Explicit": My humble apologies ... I got my forums mixed up. I normally lurk on the .Net forum, and I was in a hurry to get home last night.

[RobDog888]

No worries. I have had similar things happen when switching between VB6/VB.NET/VBA/...

Blake, I will be out for a few hours, but I think my last post should take care of the rest of the errors.

[blakemckenna]

Ok guys,

I've almost got this thing working. I have also been talking to a VBA guru from another forum. They almost have this app doing what I want it to do. However, the app is creating 2 hyperlinks in some files when it should only be creating 1. I believe this is where variables come into play using the "Range" object but I don't know how to program it. I also need to hide the target column of the hyperlink as well as the "Friendly Name" column (which will always be in Column 2).

Anyway, here is the current code that I have to date.

        For Each f2 In fj
            Set myXLApp = CreateObject("Excel.Application")
            Set myXLWrkBook = myXLApp.Workbooks.Open(f2)
            
            Set myXLWrkSheet = myXLWrkBook.Worksheets(1)
            myXLWrkBook.Application.Windows(1).Visible = True
            
            With myXLWrkSheet
                .Columns("A:V").AutoFit
                With .Rows("1:1").Font
                    .Name = "MS Sans Serif"
                    .Bold = True
                    .Size = 8.5
                End With
            End With
            
            If f2 Like "*DETAIL*" Then
                numCols = myXLWrkSheet.UsedRange.Columns.count
                With myXLWrkSheet
                    .Rows("1:1").Font.Bold = True
                    .Columns("A").Insert
                    .Range("A2", .Range("B65536").End(xlUp).Offset(0, -1)).FormulaR1C1 _
                     = "=HYPERLINK(RC[14],RC[1])"
                    .Columns("A:V").AutoFit
                    .Selection.EntireColumn.Hidden = True
                End With
            End If
            myXLWrkBook.Close True
            Set myXLWrkSheet = Nothing
            Set myXLWrkBook = Nothing
        Next

If anyone can shed light on this...I'd be grateful. By the way....the responses from this orher forum....I have to pay for. So...needless to say, free help is much better.

Thanks again for your help,

Blake

[Webtest]

You are hiding a column based on a selected range (highlighted on the sheet):

myXLWrkSheet.Selection.EntireColumn.Hidden = True

... but you have never set the selection (in the code that is shown). Either be sure to select the range you want to hide:

myXLWrkSheet.<Range object>.Select

OR - hide a range object instead of a selected range:

myXLWrkSheet.<Range object>.EntireColumn.Hidden = True

Immediately before the following line:
.Range("A2", .Range("B65536").End(xlUp).Offset(0, -1)).FormulaR1C1 _
= "=HYPERLINK(RC[14],RC[1])"

Put a temporary diagnostic popup and see if it makes any sense:

'TEST TEST TEST TEST
myXLWrkSheet.Range("A2", .Range("B65536").End(xlUp).Offset(0, -1)).Select
MsgBox (myXLWrkSheet.Range("A2", .Range("B65536").End(xlUp).Offset(0, -1)).Address)
'END TEST

[Webtest]

... and what on Earth are you trying to accomplish with this?

myXLWrkSheet.Range("A2", .Range("B65536").End(xlUp).Offset(0, -1))

[blakemckenna]

Web,

I have no idea exactly what this code is doing. All I know is that I need to create a hyperlink in Column 1, assign it the same heading as in Column 2, and then hide the Target Column and "Friendly Name" Column.

The "Friendly Name" column will always be in Column 2, however, the Target Column varies in it's location.

Can ya help???

God Bless ya,

Blake

[Webtest]

You need to hire a programmer. Sorry - I've got my own work to do right now.

[blakemckenna]

Ok bro, I understand....thanks for your help and input.

Have a great day!!!

Blake

[GeordieSi]

Realise this is a pretty old thread, but its the first hit on Google so thought I'd share my solution in case it helps anyone else...

The issue here may have been that the Macro was defined as private sub (should just be plain old sub), or it was written in an object other than Worksheet1.

'Global' macros should be placed in a module (this was my issue... only took about 3 hours to figure it out )

[blakemckenna]

This thread is so old I don't even remember opening it. I will close this immediately...