ZMBD, fantastic - thank you for that - I had never seen or used the documentor before. Looking in completely the wrong direction!
That will give me a solution for now if not entirely as slick as I was hoping for (a single click on a Form!). The documentor does show its possible to do and so I may keep pursuing it for purely acedemic reasons.
Best Rgds, Phil
Phil Davis
Last Activity: Dec 23 '22, 05:34 PM
Joined: Aug 18 '10
Location:
-
Hi ZMBD, an OpenModule request for a "FORM_module_na me" fails with a module not found condition, so I guess there is something about FORM type module names that emerge from the MODULES collection that doesn't agree with the OpenModule method (Class modules vs Standard modules maybe - its still a vague area for me!).
But actually I am making progress with my understanding of the object system. You steered me toward the ALLMODULES...Leave a comment:
-
Many thanks ZMBD, I found the following works for code contained within modules:
...Code:Dim modOpenModules As Modules Dim mdl As Module Set modOpenModules = Application.Modules For i = 0 To modOpenModules.Count - 1 Module_Name = modOpenModules(i).Name If Mid(Module_Name, 1, 5) <> "Form_" Then docmd.OpenModule Module_Name Set mdl = Modules(Module_Name)Leave a comment:
-
Programmatic Extraction of VBA Code attached to Form Controls in Access
Hi, I am trying to pull together all of the VBA code I have written into a single document.
I have worked out how to programmaticall y list all modules and "print" the code contained within using the Modules Object but code that sits behind buttons etc within a Form is proving elusive.
Possibly a bit obscure and I guess I could copy and paste but that would be deeply unsatisfying!!
Many Thanks... -
Hi Neopa, so I have two new learning curves! Excellent. I've only just learnt how enjoyable programming is without the pressure of deadlines. Hopefully I will soon(ish) be contributing to this forum rather than just taking! Once again many thanks for your help.
Rgds, PhilLeave a comment:
-
Neopa
"with a little imagination and a fair bit of experience"
Are you talking class modules? I just acquired an old VBA developer's guide with coded examples (Getz and Gilbert)!! Looks really interesting.
Cheers, PhilLeave a comment:
-
Hi NeoPa, yes I've written many a program/timer/etc etc interrupt exit in my time! You do learn the need for precision and clarity in those disciplines esp in the documentation. It's what I find most frustrating in the MS world. Thank the G for you guys on BYTES.COM and Si-The Geek (in another place).
Cheers, PhilLeave a comment:
-
Many thanks once again to you guys! I think the penny is slowly dropping!!
This morning I have written an Excel VBA to clear the contents of the test Workbooks that will be the target of my Access process. I wrote it to run from my collection in PERSONAL.xls and arranged for it to fire up a new instance of Excel under which the workbooks/sheets were activated and cleared. As an exercise it was very interesting because I now understand...Leave a comment:
-
Hi NeoPa, I've done away with the idea of a UDF to handle the Excel updates (moving the code into the calling routine instead) and life is now so much easier as you suggest!
I tend to modularise my code (habits from a past life!) and I'd still like to be able to do something to tidy up the readability of the mainline code.
When you say "Handling all of that in a single function is not an easy thing to implement, and...Leave a comment:
-
Hi NeoPa and ADezii, many thanks for your responses. I've had a fiddle following your advice in Application Automation but I think I just don't know how to reference objects that were established in the first call to my UDF from subsequent calls. These are the call to my UDF:
...Code:a = linkXL("Open", "c:\Phil\My File.xls") a = linkXL("Update", "My File.xls", "Sheet2", "CarLeave a comment:
-
Toggling Between Access and Excel in VBA
Hi, I struggling to locate how to toggle between Access and Excel within VBA code running on the Access side.
I am reading a flat (text) file to update both Access tables and Excel workbook within the same process.
No problem updating the Access table but for Excel purposes, I have created an Access VBA User Defined Function with 3 call types: OPEN, UPDATE and CLOSE that is intended to open a workbook on the first call,... -
In one sense yes - keeps the grey cells ticking over. I also have REXX syntax etdched on my brain while I find VBA function names not quite so memorable. Not to be recommended in a business environment however!!Leave a comment:
-
Hi Killer42, I'm using Excel 2002 on Win 7 - I need to get full value!!
Regarding SUBSTR - yes that was one of the easy ones - just pass the arguments to MID() - I'm writing a series of functions in VBA to mirror REXX - a language I know inside out (from my IBM mainframe background). It gives me something to do and makes me productive.
All the best, PhilLeave a comment:
-
Have now found a work around!
Rename PERSONAL.xls, create a new one, copy each module from old to new. New functions now work!!
Slightly unsatisfactory solution, I wouldn't want to do that each time the problem arose if I had many more modules. However possibly not worth wasting any brain cycles unless you're as intrigued as I am as to what the root cause of the problem is!
Best Rgds, PhilLeave a comment:
-
User Defined Function in Excel VBA not recognised
Hi, I have been quite happily adding a series of UDFs to my collection in PERSONAL.XLS until today when for no apparent reason any new routine I add does not seem to get picked up by a call, either from within (PERSONAL.xls) or any other workbook, from a cell or from within a vba subroutine.
No error is given when called from a cell, the call is simply treated as a literal string in the cell used. For example calling my new UDF substr,... -
Hi zmbd, fantastic !! Absolutely bang on. A sample shows:
Just what I would have expected and it processed in a matter of a few seconds - brilliant. Thanks so much to you and...Code:Species Square Tetrad Blackbird ST69 J Blue Tit ST69 J Greenfinch SO92 V Greenfinch SP11 Z Greenfinch SP13 F Greenfinch SP20 D Greenfinch ST59 Y Greenfinch ST69 J Greenfinch ST69 Y
Leave a comment:
-
Hi zmbd, well that has produced an interesting result rather than zero records except I'm not sure what it has returned. It returns 90k records (Species, Square, Tetrad) which if I MAKETABLE them and Group By on the Species I get 206 distinct species out of a possible 262. The commoner species such as my beloved Blackbird is not included. It seems the species that appear in virtually all locations slip the net somehow.
Unless you and...Leave a comment:
-
Hi Rabbit, have now created a Species table (Species_Table) containing a single field - Species - and holding a record for each distinct species found in the "sightings" table ([Atlas Field note book]).
I have also combined the Square and Tetrad fields into a new field - Tetradid - in the "locations" table (Glos_Tetrads) and "sightings" table ([Atlas Field note book]).
I created the following...Leave a comment:
-
Hi Rabbit, I should have said, a RIGHT JOIN produced zero records with the code I used above also.
Following your advice (I think) I just tried the following:
...Code:SELECT [Atlas Field note book].Species, [Glos_Tetrads].Square, [Glos_Tetrads].Tetrad FROM Glos_Tetrads LEFT JOIN [Atlas Field note book] ON (Glos_Tetrads.Tetrad=[Atlas Field note book].Tetrad) AND (Glos_Tetrads.Square=[Atlas Field note book].Square)
Leave a comment:
-
I'll try the code and tables again with proper formatting
...Code:SELECT [Atlas Field note book].Species, [Atlas Field note book].Square, [Atlas Field note book].Tetrad FROM [Atlas Field note book] LEFT JOIN Glos_Tetrads ON ( ([Atlas Field note book].Square = Glos_Tetrads.Square) AND ([Atlas Field note book].Tetrad = Glos_Tetrads.Tetrad)Last edited by zmbd; Sep 12 '12, 04:18 PM. Reason: (z) cleaned up the formating for ease of reading. (This would have been the question to start with :) )Leave a comment:
No activity results to display
Leave a comment: