Trying to count and display all of the formulas in Excel using Access 1

[SBendBuckeye]

Hello all,

I have done a good bit of Access programming but not a lot of Access automation where I control Excel. I have been given a huge spreadsheet and asked to put together a plan to convert it. I know it has hundreds, maybe more formula in it. I am trying to spin through all of the sheets within the workbook and count and/or display the formula for each sheet in turn.

Below is the code I am using, part of which I found on this forum a while ago.

Dim objExcel As Object
Dim sh As Excel.Worksheet
Dim oCell As Object
Dim intCounter As Integer

open workbook, set reference to Excel file, etc is working

For Each sh In objExcel.sheets
intCounter = 0
sh.Cells.SpecialCells(xlCellTypeFormulas, 23).Select
For Each oCell In Selection
intCounter = intCounter + 1
MsgBox oCell.Address & " " & oCell.Formula
Next oCell
Debug.Print objExcel.Name, sh.Name, intCounter
Next sh

Can someone please tell me what I am doing wrong? Thanks in advance for any help you can give me!

Have a great day!

 

[combo]

I have tried a middle of the loop in excel with some changes, and it works.
Try in your loop

[tt]For Each sh In objExcel.Worksheets[/tt]

Sheet is not the same as Worksheet. What is the error message?

 

[RobBroekhuis]

I think you need

For Each oCell In objExcel.Selection

Rob

 

[SBendBuckeye]

Thanks, that's exactly what the problem was. I didn't need the sheet reference at all since specialcells is for the entire workbook. Here is what I did for the benefit of anyone else who may need to list the formulas in a workbook.

Dim oCell As Object
Dim intCounter As Integer

intCounter = 0
Cells.SpecialCells(xlCellTypeFormulas, 23).Select
For Each oCell In Selection
intCounter = intCounter + 1
Debug.Print oCell.Parent.Name, oCell.Address, _
oCell.Formula
Next oCell
Debug.Print "Total Formula: "; intCounter

I really appreciate your help! That kind of error is the kind you can beat your head against for a long time when you are new to a process and aren't sure exactly what you are looking for.

Have a great day, you sure helped mine!

 

[RobBroekhuis]

Buckeye,
I'm glad you got things to work - but also confused.
"cells" is a property of a worksheet, not a workbook. So your code should only loop through the formulas on one worksheet (the active sheet). For all worksheets, you really do need your original code. Combo is right that if you have chart sheets, you should use "worksheets" instead of "sheets". If you don't, the two are equivalent.
Also, you seem to have moved away from the objExcel referencing - suggesting you tried this directly in Excel, not from within Access. I think that, when you return to Access, you'll still need to qualify your "selection" reference with the objExcel object, as I suggested above.
If I'm missing the mark, please let me know.
Rob

 

[SBendBuckeye]

Sorry for all the confusion I caused, I'm confused myself. The following code seems to work most of the time, but the linkage to the Excel file seems to be broken easily and won't work again until I reboot my computer. Here is the complete function. I define a module level object earlier and set a reference to Excel with this code before I call the function.

Any help or suggestions are very welcome as I am still not sure what exactly is happening here and if I am doing it properly. Thanks in advance for any help!

Access2000 version.

Private mobjExcel As Object

Set mobjExcel = GetObject(pstrFileNameWithPath)

Private Sub DisplayInfo(pstrFileName As String, _
pblnFrmlaCtsOnly As Boolean, pblnInclFrmla As Boolean)
Dim wb As Excel.Workbook
Dim sh As Excel.Worksheet
Dim oCell As Object
Dim intCounter As Integer
Dim intTotalCounter As Integer
Dim strFileName As String
Dim strPath As String
Dim strSQL As String
Dim strSQL2 As String
On Error Resume Next
strFileName = Mid$(pstrFileName, _
InStrRev(pstrFileName, "\", , vbDatabaseCompare) + 1)
strPath = Left$(pstrFileName, _
InStrRev(pstrFileName, "\", , vbDatabaseCompare))
strSQL = "INSERT INTO tblAutomation (FileName, Path, " _
& "SheetName, CellAddress, CellFormula) " _
& " VALUES ('" & strFileName & "', '" & strPath & "', '"
Debug.Print "File Name: "; mobjExcel.Name
' For Each sh In mobjExcel.Worksheets
' Worksheets(sh.Name).Activate
' Debug.Print "Sheet Name: "; sh.Name
If pblnInclFrmla Then
intCounter = 0
Cells.SpecialCells(xlCellTypeFormulas, 23).Select
If Err.Number Then '1004 - No cells were found.
Err.Clear
Debug.Print Tab(5); intCounter; "Formulas"
Else
For Each oCell In Selection
intCounter = intCounter + 1
intTotalCounter = intTotalCounter + 1
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL & oCell.Parent.Name _
& "', '" & oCell.Address & "', '" _
& oCell.Formula & "')"
DoCmd.SetWarnings True
If Not pblnFrmlaCtsOnly Then
Debug.Print oCell.Address; " "; oCell.Formula
End If
Next oCell
Debug.Print Tab(5); intCounter; "Formulas"
End If
End If
' Next sh
If pblnInclFrmla Then
Debug.Print Tab(5); intTotalCounter; "All Formulas"
End If
End Sub 'DisplayInfo

Thanks again for any help! Is anyone actually doing this that could post some code with the object instantiation included.

Have a great day!

 

[RobBroekhuis]

Still confused as can be. If you're running from within Access, how come you don't need to use

mobjExcel.activesheet.cells.....
or
Sh.cells......

instead of just

Cells.SpecialCells(xlCellTypeFormulas, 23).Select

Is there a cells property in Access? Similarly, is there a selection object in Access? In fact, there is no evidence at all that you use the Excel application object in your code.
I figure the answer must lie in the SQL statement above, which is gobbledegook to me. Does it somehow import the Excel workbook into Access?
Rob

 

[SBendBuckeye]

I set a Tools/Reference to Microsoft Excel 9.0 Object Library. SpecialCells is an Excel property that returns all cells from a range. That much I learned from Help but I'm still not sure that I am setting it up correctly.

The Excel input file is bigger than 6 Meg and has around 3,000 formulas in it spead over around 20 sheets. I can hardly get it to load and it is too big to maneuver around in so I'm hoping to break it down into pieces like this.

Thanks again for your interest!

By the way, In objExcel.Selection did not work, I tried it.

Have a great day!

 

[RobBroekhuis]

Right - "SpecialCells is an Excel property". That's why I'm surprised that you don't have to qualify it with your excel object variable. How much of this have you gotten to work so far?
Setting a reference to the Excel object library means that your Access VBA will understand Excel objects - but (at least in Word/Outlook/Excel VBA) you still need to qualify the actual instances of Excel objects, properties and methods with the object variable. So I'm still trying to figure out how you get it to run at all. Rob

 

[SBendBuckeye]

The code works, but it is fragile. The Excel link seems to break pretty easily. I'd be interested if it works for you if you create a little text Excel file with a couple equations in it and send it in (with a full path) to some code.

Thanks again!

 

[SBendBuckeye]

This code worked just fine. There are over 11,000 formulas in this 6+ Meg Excel file. I stripped everything out to just show the working code. If someone wants it, email me at SBendBuckeye2000@yahoo.com and I'll try to oblige although it gets pretty hairy the next few days.

Dim wb As Excel.Workbook
Dim sh As Excel.Worksheet
Dim oCell As Object

For Each sh In mobjExcel.Worksheets
Worksheets(sh.Name).Activate
Cells.SpecialCells(xlCellTypeFormulas, 23).Select
If Err.Number Then '1004 - No cells were found.
Err.Clear
Else
For Each oCell In Selection
' For Each oCell In mobjExcel.Selection <= Does NOT Work
'Put some code here
Next oCell
Next sh

mobjExcel was setup earlier at module level as object.

Thanks for all of the help!