[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Loop Through worksheets and change cell values

Posted on 2011-04-21
16
Medium Priority
?
519 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
0
Comment
Question by:johnnyg123
  • 7
  • 5
  • 4
16 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35442266
Change

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

to

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

Author Comment

by:johnnyg123
ID: 35442420
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35442628
Also change

                objExcel.ActiveWorkbook.Worksheets(lngCount).Select

to

                objExcel.ActiveWorkbook.Worksheets(lngCount).activate


and use my previous statement
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 1400 total points
ID: 35442643
You can also try this more efficient alternative

                Set aws = objExcel.ActiveWorkbook.Worksheets(lngCount)
                aws.Cells(i, 24).Value = ""
0
 

Author Comment

by:johnnyg123
ID: 35442933
hmmmm

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

Author Comment

by:johnnyg123
ID: 35442961
I have attached mdb
blankTimeSheets.mdb
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35443015
insert this statement between the above two lines

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

and check whether these names are as expected.

0
 

Author Comment

by:johnnyg123
ID: 35443201
here is an updated version of the mdb

the names look as expected

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

Expert Comment

by:Saqib Husain, Syed
ID: 35443257
Do you see the excel files open on screen while the program runs?
0
 

Author Comment

by:johnnyg123
ID: 35443452
nope

0
 
LVL 10

Expert Comment

by:broro183
ID: 35443889
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
 
LVL 10

Assisted Solution

by:broro183
broro183 earned 600 total points
ID: 35444083
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
 

Author Comment

by:johnnyg123
ID: 35444093
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
 

Author Comment

by:johnnyg123
ID: 35444145
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
 
LVL 10

Expert Comment

by:broro183
ID: 35444164
*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
 
LVL 10

Expert Comment

by:broro183
ID: 35444235
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question