Some tips

• understand scope in VBA – this is NOT VB.net
• perhaps use global variables and use _CAPS so they are readily identified
• put them in their own VBA module

With VBA, it is generally a solution for one’s self or a small group of individuals. Most of my apps if an exception happens, a yellow line is highlighted in the VBA editor, you investigate the variables LIVE (on thy fly – love that about VBA) and adjust or fix the situation. If the code is for others, then some error handling is added and the world is stable until the next time.

Recently, I was asked to make the error handling cascade like c# – so now it is time to fully understand the error handling of VBA.

Here is SUPER resource – it is for MS Access, but will apply equally for all MS Office and other VBA apps. There are few new tricks I will try on some hornery AutoCAD tools. In Excel the “Do Events” usuallly fixes many of the super odd errors (VBA is going faster than Excel and.. Do Events helps to sync the two into stability.

So there is an application that requires a best guess – but still human interaction to choose which item when there is more than one result returned.

So one can make a web service to pull back a pull down and make a form for the user to make a choice allowing quicker spreadsheets to be made. Why not web services and in my case, ASP.NET can be used.

Here is a REALLY old article, but it has all the pieces. http://msdn.microsoft.com/en-us/magazine/cc163837.aspx#S1

http://stackoverflow.com/questions/8249706/run-stored-procedure-and-return-values-from-vba

http://stackoverflow.com/questions/1120674/accessing-sql-database-in-excel-vba

http://www.excelforum.com/excel-general/758998-data-validation-drop-down-menu-from-a-database.html

http://www.erlandsendata.no/english/index.php?t=envbadac

OK – so I am reading stuff that seems a little too geeky, but after asking “what is this late vs. early binding” stuff – I had to know what it was. Here is a simple explanation – even I can understand. It really does (did) not matter – but the main point is summarized nicely in this article “… as long as the programmer uses it responsibly (late binding) “.

http://stackoverflow.com/questions/10580/what-is-early-and-late-binding

What is this about? I want to use Dictionaries in VBA … now I have finally snapped and found out. Here is THAT article …

http://stackoverflow.com/a/23675963 and the ref to the msdn to it is here.

http://www.brianweidenbaum.com/projects/vba-arraylist/

A friend mentioned this to me. Because I am working for an EPC (Engineering Procurement Company) that builds 3D models, I immediately put VR and Navis together. It turns out he was just mentioning a cool concept. Nonetheless – if this can all be put together for an EPC, a virtual head tour might not be far behind.

Oculous: http://www.oculusvr.com/blog/announcing-the-oculus-rift-development-kit-2-dk2/

Recently, I made an excel spreadsheet stacker where, given a list of excel workbook paths, it would stack them all into one. Then I thought, this would be beyond great to stack Bentley Spec tables on top of one another so we can do a search and troubleshoot quicker.

In short, open up the SpecTables table of each, export those tables that we find to Excel sheets, then use my stacker and stack them all on top of one another. Since the stacker removes any need to worry about column order – AND since it tells you what columns are “lost” we can run it many times until everything is under one umbrella.

Here are the links that helped me export from MS Access to Excel sheets but using Excel as the engine which holds the VBA that makes this all work

• http://stackoverflow.com/questions/1849580/export-ms-access-tables-through-vba-to-an-excel-spreadsheet-in-same-directory
  • here was the heart of the macro tip from this guy

[vb]

Dim outputFileName As String
outputFileName = CurrentProject.Path & "\Export_" & Format(Date, "yyyyMMdd") & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table1", outputFileName , True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table2", outputFileName

[/vb]

• http://www.mrexcel.com/forum/excel-questions/643188-excel-run-access-docmd-transferspreadsheet-into-activeworkbook.html

I had to add the MS Access reference (I am using both version 14 or MS Office 2010) and the reference for DAO 3.6 through the “Tools-Add Reference” menu in Excel’s VBA editor.

Here I am writing to a client explaining which technology is the best. The end use is a one table, muliti-report application. Perhaps a few tables with selection lists, but the main data is in one table. The 2nd phase adds 2 more tables. This company is not a Windows shop, but has no Macs or iOS devices per-se. All the desktops are for Windows.

An executive summary is that if you want to do a corporate application that is the most flexible and might have complicated interrelationships then the non-all-in-one approach is the best solution using one tool for the data storage (the database or dB), another to make the input screens (ASPX, .net, php, html) and another for reporting like reporting services or even MS Access – it is good for that but only that. This requires having the resources and time to get this completed. A hybrid approach is always an option as the tools discussed in this article do interact with each other.

If you want to get going, make large even mid-complex databases and get your work done fast and easy and have many reports that are very quick to develop by even the most basic database designers or business people – Filemaker wins hands down.

Again, if you feel you need to grow, a hybrid system would work best. There are not lots of us who know so many technologies though, and it limits things to more advanced or experienced people – hence the cost might increase.

Rarely should one use MS Access even if it is already installed and tempting you to click on it on your start bar. Continue reading →

This is a WT* moment in my opinion. Filemaker Go does not support the Import script function except from Filemaker databases and NOT from XML datasources from a website. Enter “Import From URL script step” to solve my issue. For the interim this means that for a work-around for my deadline, I had to copy the dB, work with Filemaker Pro desktop version to do the import, then upload this 2nd file to Filemaker Go a 2nd database. The alternative was to change the special API I made to prep the data into a properly formatted Filemaker FMRESULT xml file. The Import script step does NOT tell you on the help page this is the case – just the FM Go developer guide and the “NOT COMPATIBLE” text (the script line does NOT turn grey). This means at least 30% of the work I did to make the XML output from the other system was wasted effort.  I fought a little with the aspx that worked on a previous project and found that with the new .NET 4.5, I had to export an application\xml header explicitly before spitting out the XML data – had I known, I would have not had to fight because exporting text on a webpage is VERY clear to me. Alas, lesson learned – use Import From URL and parse text. This seems like a step backwards.

Well, I think there is hope to get my one FM dB on Filemaker Go by using the Import from URL script step. This guy as a great article about the topic which I wanted to save and share. I will have to export the data in a simple format, but I can use a loop through the text and import one record at a time from this text. I THINK I also need to put a retry loop in the system and a marker for end of data to ensure I have all the data (perhaps I don’t require it, but paranoia and only doing this once reigns!

OK – the title is misleading on purpose – in short you can’t do it via programming. You can for MS Project though. For excel all I have to say is …. Man people have too much spare time. Now … so do I !!! Incredible – how to add a ribbon item IN a xlsm file. It disappears once the workbook is closed though.

Once the utility is installed it is in your start menu called ‘Custom UI Editor For Microsoft Office’

http://stackoverflow.com/questions/8850836/how-to-add-a-custom-ribbon-tab-using-vba

First, I am using RS 2008 – not SP1 or anything. This is quite frustrating but I know 2014 is coming soon (many old bugs fixed), but alas, here are some tips of things that I am struggling with and others have too.

Use Consistent Units

First, if you are getting extra columns and/or rows and you want contiguous (no hidden ones) in between – make sure ALL YOUR UNITS ARE THE SAME. Use inches with inches and cm with cm’s. Also align the “top” and “left” numbers to 0’s when possible. The main point is use numbers like 1.7in or 3.5cm and not 1.45235in. If you get numbers like that AND you did align (snap) to other objects, most likely something is in another unit that you are snapping to – or that IT is aligned to.

Headers not consistent when exporting to Excel

This guy has the key. Use Can Grow and Can Shrink to “False”