UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Loop Through Table To Update Many Excel Documents, Access 2010    
 
   
SubPlanner
post Jan 10 2017, 12:25 PM
Post#1



Posts: 34
Joined: 22-April 16



I need to loop through a table I created and open / save and close external Excel documents.
There are about 165 external Excel documents.

I have a form created in Access that allows users to pick what documents they want to update.
Once they make their picks, they hit a button to launch the new loop code.
They could pick 1 or 2. Or they could pick 20 or 40 of them.

So what I think I need is a fancy loop code that would loop through a table of file names and file directories, then open the excel documents and save/close them.
The table is named "ExcelFiles" and 164 Excel document names inside the table are in the column named FName, and a file directory location in a column named FPath.

Thanks for any help you can offer
SubPlanner.
Go to the top of the page
 
DanielPineault
post Jan 10 2017, 01:23 PM
Post#2


UtterAccess VIP
Posts: 5,451
Joined: 30-June 11



You can directly use the form's recordset and filter to only process those selected records. Below is a general concept (aircode)

CODE
    Dim rs                    As DAO.Recordset 'Could be Object
    Dim rsFiltered            As DAO.Recordset 'Could be Object

    Set rs = Me.RecordsetClone
    rs.Filter = "[RecordSelected] = True"
    Set rsFiltered = rs.OpenRecordset
    With rsFiltered
        If .RecordCount<>0 Then
            Do While not .EOF
                'Process the select Excel WorkBook as required
                .Movenext
            Loop
        End If
    End With

    rsFiltered.Close
    rs.Close
    Set rsFiltered = Nothing
    Set rs = Nothing


The above assumes you have a control named RecordSelected which would be a Yes/No checkbox which the user uses to identify which records they wish to process. If you have another approach you'd need to explain it. Also, do not forget proper error handling!


Also, for optimum performance, you're probably best to only create a single Excel object at the beginning of your procedure and using it over and over and only destroying it at the very end, otherwise you'll be creating/destroying your Excel instance over and over and this can cause useless performance issues and gobble up memory to the point of crashing your app. (always try GetObject prior to CreateObject)

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
kfield7
post Jan 10 2017, 01:38 PM
Post#3



Posts: 775
Joined: 12-November 03
From: Iowa Lot


had a thought but re-thunk.
Go to the top of the page
 
doctor9
post Jan 10 2017, 01:48 PM
Post#4


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


SubPlanner,

> allows users to pick what documents they want to update.

What sort of update are we talking about? Is the user manually making changes to each workbook? If so, your loop should just OPEN the workbooks and make the Excel application visible after doing so. But if you want to use code to perform the update, that's a different story.

> open the excel documents and save/close them

Opening, saving and closing wouldn't do anything, surely. You need to perform the update between opening and closing the file.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
DanielPineault
post Jan 10 2017, 01:50 PM
Post#5


UtterAccess VIP
Posts: 5,451
Joined: 30-June 11



CODE
had a thought but re-thunk.

iconfused.gif

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
SubPlanner
post Jan 10 2017, 03:12 PM
Post#6



Posts: 34
Joined: 22-April 16



The Excel documents have an internal code that will run on save to do other functions.

I have attached a copy of the DB I am using. Hope that helps.

SubPlanner
Attached File(s)
Attached File  UpdateSelective_Test.zip ( 101.39K )Number of downloads: 11
 
Go to the top of the page
 
doctor9
post Jan 10 2017, 03:14 PM
Post#7


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


SubPlanner,

> The Excel documents have an internal code that will run on save to do other functions.

Silly question: Would it make sense to move that internal code to the files' Open event rather than the Save event? That way the update happens when the file is opened, and you don't need the user to select files to update.

Just a thought.

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
SubPlanner
post Jan 13 2017, 07:23 AM
Post#8



Posts: 34
Joined: 22-April 16



Sure, either way...

But I still need to solve the loop through table problem.


SP
Go to the top of the page
 
SubPlanner
post Jan 16 2017, 10:08 AM
Post#9



Posts: 34
Joined: 22-April 16



Any help on this ?

Sp.
Go to the top of the page
 
projecttoday
post Jan 16 2017, 10:17 AM
Post#10


UtterAccess VIP
Posts: 8,683
Joined: 10-February 04
From: South Charleston, WV


If you want to loop through selections, then maybe a listbox. See the current thread "Print Multiple Reports from Query".

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
SubPlanner
post Jan 16 2017, 12:39 PM
Post#11



Posts: 34
Joined: 22-April 16



I have all the stuff I need to pick the data.
Now I just need some fancy loop code to loop through the table and actually go out and open the chosen excel documents.


Thanks for your reply.
SubPlanner
Go to the top of the page
 
projecttoday
post Jan 16 2017, 01:43 PM
Post#12


UtterAccess VIP
Posts: 8,683
Joined: 10-February 04
From: South Charleston, WV


Then recordset code.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
SubPlanner
post Jan 17 2017, 05:37 AM
Post#13



Posts: 34
Joined: 22-April 16



I am still learning VBA. I am a little rusty on loop code.
I put a sample database in a zip back a few days ago.

Thanks for your help
SubPlanner
Go to the top of the page
 
projecttoday
post Jan 17 2017, 05:51 AM
Post#14


UtterAccess VIP
Posts: 8,683
Joined: 10-February 04
From: South Charleston, WV


There is some recordset code in post #2 of this thread.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
DanielPineault
post Jan 17 2017, 10:25 AM
Post#15


UtterAccess VIP
Posts: 5,451
Joined: 30-June 11



I took a quick look and below is a quick example of what would need to be done for the multiple update button

CODE
Private Sub Command14_Click()    'Multiple Selections
    On Error GoTo Error_Handler
    Dim rs                    As DAO.Recordset    'Could be Object
    Dim rsFiltered            As DAO.Recordset    'Could be Object
    Dim ans                   As Long

    ans = MsgBox("Are you sure you want to Run these Multiple Updates?.", vbYesNo, "Multiple Update Check")
    If ans = vbYes Then
        Set rs = Me.FRM_SelectUpdateSub2.Form.RecordsetClone
        rs.Filter = "[Select] = True"
        Set rsFiltered = rs.OpenRecordset
        With rsFiltered
            If .RecordCount <> 0 Then
                Do While Not .EOF
                    'Process the select Excel WorkBook as required
                    MsgBox "Processing " & ![Building and Floor #]
                    'Here you could call a seperate function to do whatever you want to each Excel WorkBook
                    'Call ProcessXLS("")
                    .MoveNext
                Loop
            End If
        End With

        MsgBox "Stuff Done", vbOKOnly, "Do Stuff Request"
    ElseIf ans = vbNo Then
        MsgBox "Multiple Update Cancelled", vbOKOnly, "Cancel Request"
    End If

Error_Handler_Exit:
    On Error Resume Next
    rsFiltered.Close
    rs.Close
    Set rsFiltered = Nothing
    Set rs = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Command14_Click" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub


Also, give your controls meaningful names so you can find yourself in your code. Names like Command14 are the default, I know, but don't use them. Name it properly, something like cmd_UpdateMultipleWrkBks for instance, then when you review your code, you'll instantaneously know what button's code you are on. This also becomes more meaningful information for the error handler.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
DanielPineault
post Jan 17 2017, 10:29 AM
Post#16


UtterAccess VIP
Posts: 5,451
Joined: 30-June 11



Another issue I am spotting is the fact that you seem to have to connection between [Building and Floor #] and then the file names and paths for the actual Excel files. iconfused.gif I think you'd need to add a [Building and Floor #] column to your TBL_ExcelImpData table and use it for the selection process then we could easily retrieve the associate path/filename and process the file.

Also, never use Reserved Words to name anything in your database to avoid problems. So the field name select is not a good idea.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
SubPlanner
post Jan 17 2017, 01:05 PM
Post#17



Posts: 34
Joined: 22-April 16



Thanks for the code and the tips.

I will test it out and let you know how it worked.

SubPlanner
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    15th December 2017 - 09:03 AM