ActiveX component can't create object?

am getting the above error message in some VBA code. Here is the line of code where I am getting the error message.

Set xlSheet = CreateObject("Excel.Worksheet")


This code is within an Access database and I am trying to create Excel spreadsheet files. I am new to VBA so I'm kinda brain dead...please bare with me. I have also attached the function that has this line of code as well.

Code:

---

Function formatXLFiles(userChoice As Boolean)
    Dim xlApp, xlSheet, myXLWrkBook    As Object
    Dim databasePath
    Dim XLMacroPath
   
    databasePath = ExtractPath(CurrentDb.Name)
    XLMacroPath = databasePath & "\FormatFieldReports.xls"
   
    'Open connection to Excel Workbook and make it visible
    Set xlApp = CreateObject("Excel.Application")
    Set myXLWrkBook = GetObject(XLMacroPath)
     
    myXLWrkBook.Application.Visible = True
    myXLWrkBook.Application.Windows(1).Visible = True
   
    Set xlSheet = CreateObject("Excel.Worksheet")
    xlApp.Application.Workbooks.Open XLMacroPath
    xlApp.xlSheet.Columns.autofit = True
   
    'Run procedure in ThisWorkBook folder
    If userChoice Then
        myXLWrkBook.Application.Run "ThisWorkbook.openForProcessing(true)"
    Else
        myXLWrkBook.Application.Run "ThisWorkbook.openForProcessing(false)"
    End If
   
    'Close Automation object
    'Either invoke the save method or set the Saved property to true to avoid a prompt about saving changes
    'myXLWrkBook.Application.ActiveWorkbook.Save
    myXLWrkBook.Application.ActiveWorkbook.Saved = True
    myXLWrkBook.Application.ActiveWorkbook.Close
   
    Set myXLWrkBook = Nothing
   
    xlApp.Quit
    Set xlApp = Nothing
End Function

---

Thanks for your help in advance.

Blake

The first thing I'd do is check your references to make sure you have one for Excel. In the code window, click Tools -> References on the menu bar. Check if you have a checked box near the top that says something like "Microsoft Excel 9.0 Object Library" (your number may be different), and one that says something like "Microsoft ActiveX Data Objects 3.6 Library" (again, number may be different).
If not, scroll down and check the box for them, normally choosing the highest numbered one if you have more than one. You might choose a lower one if your users are on older systems, or you could update them.
Access sometimes loses the references and you have to go back in and check them again.

Haven't read all the way through your code, but I've used the following successfully:

VB Code:

Dim fName As String
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlRange As Excel.Range
Set xlApp = CreateObject("Excel.Application")
fName = "c:\path\filename"
Set xlBook = xlApp.Workbooks.Open(FileName:=fName)
Set xlSheet = xlBook.Sheets(1)

VBAhack

Hack,

Here is a more refined and "almost finished" version of my code. One problem right now is trying to hide several columns and assign the heading value of column 2 to column 1.

Another problem, since each xls file is unique it it's layout, is determining what column the "Target" column is for the hyperlink. I'm assuming you would need to perform a looping function to determine that but I'm new to VBA so I'm not real sure.

Anyway, here is the code if ya feel like takin' a stab at it.

Thanks in advance,

Blake

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 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])"
'                    .Selection.EntireColumn.Hidden = True
                    .Columns("A:V").AutoFit
                End With
            End If
            myXLWrkBook.Close True
            Set myXLWrkSheet = Nothing
            Set myXLWrkBook = Nothing
        Next
    End If

---

By the way Hack, I keep getting the error message:

"Object doesn't support this property or method"

on the line

' .Selection.EntireColumn.Hidden = True

Quote:

---

Originally Posted by blakemckenna

I keep getting the error message:

"Object doesn't support this property or method"

on the line

' .Selection.EntireColumn.Hidden = True

---

I think the reason is that nothing ever got selected.

VBAhack

Actually hack it still gave me the same message.

Didn't realize you had the "Selection.xxx" line commented out. Thus, it appears to be failing on the previous line. Take a look at:

VB Code:

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

maybe something's wrong with it.

VBAhack