We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Loop Through worksheets and change cell values

Medium Priority
540 Views
Last Modified: 2012-05-11
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
Comment
Watch Question

Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
Change

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

to

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

Author

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
Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
Also change

                objExcel.ActiveWorkbook.Worksheets(lngCount).Select

to

                objExcel.ActiveWorkbook.Worksheets(lngCount).activate


and use my previous statement
Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
hmmmm

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

Author

Commented:
I have attached mdb
blankTimeSheets.mdb
Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
insert this statement between the above two lines

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

and check whether these names are as expected.

Author

Commented:
here is an updated version of the mdb

the names look as expected

can't understand why the value remains unchanged
blankTimeSheets.mdb
Saqib HusainEngineer
CERTIFIED EXPERT

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

Author

Commented:
nope

CERTIFIED EXPERT

Commented:
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

CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

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!

Author

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

CERTIFIED EXPERT

Commented:
*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
CERTIFIED EXPERT

Commented:
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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.