Loop Through worksheets and change cell values

Change

objExcel.cells(i, 24).Value = ""

to

activesheet.cells(i, 24).Value = ""

well...I tried activesheet.cells(i, 24).Value = ""  but I get an object error message so then I tried

objExcel.activesheet.cells(i, 24).Value = ""  which did not reult in an error message but the value was the same as before.  (it was not changed to blank)

I have attached a screen shot of an example.  The 88 is stil in i24
example-workbook.doc

Also change

                objExcel.ActiveWorkbook.Worksheets(lngCount).Select

to

                objExcel.ActiveWorkbook.Worksheets(lngCount).activate


and use my previous statement

hmmmm

no change .... value is not being saved as blank....(remains unchanged)

I have attached mdb
blankTimeSheets.mdb

insert this statement between the above two lines

msgbox(aws.parent.name & vbcrlf & aws.name)

and check whether these names are as expected.

here is an updated version of the mdb

the names look as expected

can't understand why the value remains unchanged
blankTimeSheets.mdb

Do you see the excel files open on screen while the program runs?

nope

I'm having a look at this too to fill in some time & my first suggestion is to add "Option Explicit" at the top of every module, try compiling the code & then fix the undeclared variable that pops up.

I haven't read the code properly yet, but you could try

aws.Cells(i, 24).clearcontents

Select allOpen in new window

Well ....I figured out what is going on

My open statement is opening the file in read only mode

objExcel.Workbooks.Open strPathFile, , True  (I thought the true was for update mode....ooops)



This causes the files to be saved in the my documents folder on my c drive.

(Rather than the directory from where it was opened which is where I was hoping it would)

I figured this out when I tried to force a saveas into the strPathFile and got the

error saying the file was opened as read only.  


for the record the following is the code that works the way I was looking for

Private Sub cmdZeroSales_Click()
 
    On Error GoTo Err_cmdZeroSales
   
    Dim sFolder As String
    Dim fso As Scripting.FileSystemObject
    Dim aFiles As Variant
    Dim i As Integer
    Dim J As Integer
   
    Dim ExcelFilePath As String
    Dim ExcelFolder As String
   
    Dim strPathFile As String
   
   
    Dim blErrors As Boolean
   
   
    blErrors = False
   
    Set fso = New Scripting.FileSystemObject
   
    aFiles = apiBrowseFiles("Select Time Sheets to Zero Sales", "D:\", , "Excel (*.xls)" & Chr(0) & "*.xls" & Chr(0))
   
    If IsArray(aFiles) Then
   
        Dim strFileName As String
       
        Dim objExcel As Object, objWorkbook As Object, aws As Object
       
        For i = 1 To UBound(aFiles)
       
            ExcelFilePath = aFiles(i)
           
            strPathFile = ExcelFilePath
           
            Set objExcel = CreateObject("Excel.Application")
           
            objExcel.Workbooks.Open strPathFile
                       
            For lngCount = 2 To objExcel.Worksheets.Count - 1
           
                               
                Set aws = objExcel.activeworkbook.Worksheets(lngCount)
               
                'MsgBox (aws.Parent.name & vbCrLf & aws.name & vbCrLf & strPathFile)
               
                aws.Range("I24").Value = ""
                aws.Range("I25").Value = ""
                aws.Range("I26").Value = ""
                aws.Range("I27").Value = ""
                aws.Range("I28").Value = ""
                aws.Range("I29").Value = ""
                       
           
            Next lngCount
           
            'objExcel.Application.DisplayAlerts = False
           
            objExcel.activeworkbook.Save
           
            objExcel.activeworkbook.Close
           
            objExcel.Quit
            Set objExcel = Nothing
       
        Next i
         
        Set objWorkbook = Nothing
        Set fso = Nothing
   
    Else
        MsgBox "Process Cancelled", vbOKOnly, "No Files Selected"
        Exit Sub
    End If
         
Exit_cmdZeroSales:

    If blErrors = False Then
        MsgBox ("Process Complete")
    Else
        MsgBox "Error in process .... please fix error and rerun"
    End If
       
    Exit Sub
   
Err_cmdZeroSales:

    blErrors = True
   
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_cmdZeroSales
   
End Sub




Thanks for all the posts!

broro183,

you are correct that the source of my problem is that I was opening in read only mode
(didn't see your post until after I submitted my comment)

I do want to give you some points for pointing that out.

Since ssaqibh helped me with the formatting I will award more points

*chuckle* I've spotted why you aren't seeing any files on the screen.

It will probably run faster when you can't see the excel application, but if you want the reassurance of being able to see it you can replace the below line of code:

Set objExcel = CreateObject("Excel.Application")
'change the above line (as it is in my previous post) to the below lines
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

'and if you want it to remain open at the end of the code you can replace
        objExcel.Quit
'with this line...
        objExcel.UserControl = True

Select allOpen in new window

hth
Rob

ditto - I didn't see your last post either :-)

aws.Range("I24").Value = ""
                aws.Range("I25").Value = ""
                aws.Range("I26").Value = ""
                aws.Range("I27").Value = ""
                aws.Range("I28").Value = ""
                aws.Range("I29").Value = ""
'can be changed to
aws.Range("I24:I29").clearcontents

Select allOpen in new window

Thanks Johnny :-)
Here are some general suggestions:
 - for ease of reading the code & maintainability I recommend deleting lines of code & declarations that aren't being used for example the "fso".
- change "as Integer" to "as Long" because most new computers (perhaps since VB6?) internally convert integers to long before working with them, so why not save the computer the work.
-minimise any code inside loops to the actions that have to be repeated. For example,  move the Dim statements out (VBA doesn't recognise "nested scope") & don't close the excel app in the loop as you can use it repeatedly without the overhead of repeated opening & closing.


hth
Rob