Loop Through worksheets and change cell values

I have an access 2003 form that I would like to perform the following functionality when a command button is clicked.

1.  Allow user to select multiple excel workbooks

2.  for each of the workbooks selected, loop through each of the worksheets and change cells
      i24, i25, i26, i27, i28 and i29 to blank.

   (The first worksheet will be a summary and should be skipped.  Each workbook will have a different amount of worksheets)

3. save the workbook changes

So far I have the following code which seems to allow selection of workbooks and looping through worksheets but the setting of cell values is not occurring


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
       
        For i = 1 To UBound(aFiles)
       
            ExcelFilePath = aFiles(i)
           
            strPathFile = ExcelFilePath
           
            Set objExcel = CreateObject("Excel.Application")
           
            objExcel.Workbooks.Open strPathFile, , True
                       
            For lngCount = 2 To objExcel.worksheets.Count - 1
           
                objExcel.Activeworkbook.worksheets(lngCount).select
               
                objExcel.cells(i, 24).Value = ""
                objExcel.cells(i, 25).Value = ""
                objExcel.cells(i, 26).Value = ""
                objExcel.cells(i, 27).Value = ""
                objExcel.cells(i, 28).Value = ""
                objExcel.cells(i, 29).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
johnnyg123Asked:
Who is Participating?
 
Saqib Husain, SyedEngineerCommented:
You can also try this more efficient alternative

                Set aws = objExcel.ActiveWorkbook.Worksheets(lngCount)
                aws.Cells(i, 24).Value = ""
0
 
Saqib Husain, SyedEngineerCommented:
Change

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

to

activesheet.cells(i, 24).Value = ""
0
 
johnnyg123Author Commented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Saqib Husain, SyedEngineerCommented:
Also change

                objExcel.ActiveWorkbook.Worksheets(lngCount).Select

to

                objExcel.ActiveWorkbook.Worksheets(lngCount).activate


and use my previous statement
0
 
johnnyg123Author Commented:
hmmmm

no change .... value is not being saved as blank....(remains unchanged)
0
 
johnnyg123Author Commented:
I have attached mdb
blankTimeSheets.mdb
0
 
Saqib Husain, SyedEngineerCommented:
insert this statement between the above two lines

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

and check whether these names are as expected.

0
 
johnnyg123Author Commented:
here is an updated version of the mdb

the names look as expected

can't understand why the value remains unchanged
blankTimeSheets.mdb
0
 
Saqib Husain, SyedEngineerCommented:
Do you see the excel files open on screen while the program runs?
0
 
johnnyg123Author Commented:
nope

0
 
broro183Commented:
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

Open in new window

0
 
broro183Commented:
Hi,

I've made a few changes in the below code but haven 't investigated any of the other functions/subs as they look as if they have been written & tested (ie copied from somewhere? ;-)).

I could be wrong... but I believe the main issue was that the files were being opened as read only, then the display of alerts was turned off before saving was attempted, thus meaning the errors were never seen.
However, this doesn't explain why you aren't seeing any files - me thinks that may need more investigation.

While developing the code, I recommend setting a Reference via VBE - Tools - References to the "Microsoft Excel x.x" library. This will allow you to use "early binding" & also let inteli-sense work. I did this while working through the code & have left the object types commented out next to the "as Object" lines.

btw, I suggest moving the majority of this code into a normal module & then calling the new code from the button click event sub in the Form module.

I hope I haven't missed anything - here goes...

Option Compare Database
Option Explicit

Private Sub cmdZeroSales_Click()
  
    On Error GoTo Err_cmdZeroSales
    
    Dim sFolder As String
    Dim aFiles As Variant
    Dim i As Long
    Dim J As Long
    
    Dim ExcelFolder As String
    
    Dim strPathFile As String
    Dim blErrors As Boolean
    Dim strFileName As String
    Dim objExcel As Object 'Excel.Application
    Dim objWorkbook As Object 'Excel.Workbook
    Dim aws As Object 'Excel.Worksheet
    Dim lngCount As Long
    
    
    
    aFiles = apiBrowseFiles("Select Time Sheets to Zero Sales", "D:\", , "Excel (*.xls)" & Chr(0) & "*.xls" & Chr(0))
    
    If IsArray(aFiles) Then
            
        Set objExcel = CreateObject("Excel.Application")
        
        For i = LBound(aFiles) To UBound(aFiles)
            strPathFile = aFiles(i)
            'this is what was probably causing the errors (compare it with your last version...)
            Set objWorkbook = objExcel.Workbooks.Open(strPathFile, , False)
                        
            For lngCount = 2 To objWorkbook.Worksheets.Count - 1
            
                Set aws = objWorkbook.Worksheets(lngCount)
                    MsgBox (aws.Parent.name & vbCrLf & aws.name & vbCrLf & strPathFile)
                    aws.Cells(i, 24).clearcontents
                Set aws = Nothing
            Next lngCount
            
            objWorkbook.Close True
        Next i
          
        Set objWorkbook = Nothing
        objExcel.Quit
        Set objExcel = 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

Open in new window


hth
Rob
0
 
johnnyg123Author Commented:
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!
0
 
johnnyg123Author Commented:
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

0
 
broro183Commented:
*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

Open in new window


hth
Rob
0
 
broro183Commented:
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

Open 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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.