I keep getting asked how do I do a simple dialog box in VBA that allows the user to select a spreadsheet and then assign the spreadsheet to an object. Below is a very simple idea that I take and extend if I need to. Obvious things that are missing include error checking to make sure the file is the right type but I don’t need to do that every time.
Dim xlExcel As Excel.Application
Dim strExcelFilePath As String
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
If .Show Then strExcelFilePath = .SelectedItems(1)
End With
Set xlExcel = New Excel.Application
xlExcel.Workbooks.Open strExcelFilePath, , True
Once I have the object I can then select worksheets and do pretty much everything I could do in Excel VBA via the code in Access for example.
Are there other ways that would make more sense? Post your thoughts below.
I have recently obtained my PRINCE2 Foundation certification which means I am halfway towards becoming a registered PRINCE2 Practitioner. PRINCE2 covers the management, control and organisation of a project and has become a recognised standard for project management in the UK and lately in other countries too. The PRINCE2 methodology is fully scalable which means it can be used for small projects and also large corporate projects. However this doesn’t mean it is always suitable for small projects. Sometimes it can feel like overkill especially if it is implemented by an inexperienced project manager.
Continue reading ‘The Principles Of Project Management Book’
In Transact-SQL scripts the best and easiest way to check if a table exists is to check the OBJECT_ID value and if it is not a NULL value then the table exists.
-- Drops the table.
IF OBJECT_ID('MyTable') IS NOT NULL DROP TABLE [MyTable]
However if you have created a temporary table then the command above won’t find the table and causes problems when you try to create a new table with the same name. The way round this is to use the code below.
-- Drops the temporary table.
IF OBJECT_ID('tempdb..##Temp') IS NOT NULL DROP TABLE ##Temp
I hope this helps you because it had me stumped for quite a while!
Part one of my book review.
Book page on Packt Publishing website.
If this book was split into two parts then that would be the end of the first part because you now have a blog set up looking how you want it to with various plugins in place to allow you to produce content in the way you want to. The second part of the book is focused on getting your content known by other like-minded bloggers and also getting your place on the many search engines and blog websites on the internet. Chapter six looks at the principles of Search Engine Optimisation or SEO. This is another topic that deserves a whole book to itself but the basics are well versed with good examples of plugins and ideas for you to optimise your blog for the ideal placing on search engines. Various ideas are discussed such as Google sitemaps, optimising the robots.txt file on your web server and submitting your blog details to search engines.
The next chapter follows on with more powerful promotion of your blog which requires you to spent a bit more time on this but the rewards outweigh the time spent as your blog will get more popular and eventually potentially one of the more well known blogs on the internet. RSS syndication using Feedburner is explained along with linking your blog to Technorati, social network sites, Twitter, various social bookmarking sites and in the next chapter attention is given to interacting with other like-minded bloggers within the blogosphere and creating a means for your viewers to get in touch via contact forms and comments.
Continue reading ‘WordPress For Business Bloggers Book Review Part 2′
WordPress for Business Bloggers book cover
Book page on Packt Publishing website
I have played with the idea of maintaining a blog for a while now. I come across unique challenges every day and I think that it would be a great thing to record how I solved the problems so that I can refer to it in future if I need to and also hopefully other people could understand how I solved the problem and learn something from it too. Blogs have been around now for a while on the internet and they cover any topic from your family to marketing for a product.
For the software to run my blog on I chose WordPress which is a very powerful open source application with a lot of support from people who are willing to develop plugins and themes for it. I already have a web server I can install the application on and start my blog so all I needed was a book that would guide me step by step through the minefield of setting up a blog and using it in the right way. That is when I found Paul Thewlis had written a book called ‘WordPress for Business Bloggers’ which was just what I was looking for. After using the book extensively to set up my blog I have written a review of the book.
Continue reading ‘WordPress For Business Bloggers Book Review Part 1′
I am currently working on an Access database application where the project brief is for the same data to be ‘cut’ in many different ways. For this I decided to use a report builder form which will allow the end user to extract the information however they like.
Because of the Data Protection act I won’t be able to publish my work here but I will create a simple example to demonstrate a function I created called ‘FilterByALLCheck’ which takes the name of a Form and the name of a ListBox control. The function would be called in the OnChange event of the ListBox and it simply loops through the ItemsSelected property of the ListBox and if the ‘<ALL>’ option is selected then remove all the other selected items and just keep the ‘<ALL>’ option.
Continue reading ‘Select/Unselect Selected Values In A Form ListBox Using VBA’
It has recently been announced by Scott Guthrie from Microsoft that jQuery is going to be shipped with their Visual Studio product from now on.
jQuery is an open source JavaScript library which simplifies tasks such as event handling and Ajax interaction in a bid to speed up web development and it does it very well. It is a very concise language and well documented with a large fan base across the internet which explains why Microsoft have picked it up.
What has perhaps been overlooked is the fact that Google announced in May 2008 that they will start to host the jQuery libraries which has three benefits:
- Caching will be sorted out by Google and updating of files will be automatic based on how clever you are with the version numbering. More later on in this post.
- If somebody else references the libraries in their script then it will be able to use the cached version which avoids several versions of the jQuery library on the same machine.
- Bandwidth for serving the libraries is now consumed by Google which is a factor if you have a busy website.
Continue reading ‘Differences Between Local And Google Hosted jQuery Installations’
I always try and stick to the Hungarian notation naming convention when I code in Access. This allows me to have no doubt about the data type of the variable. For example ‘DateOfBirth’ could be a String with the full text in, a Date field with a standard date in or an Integer field with the date in serial form. However having a variable called ‘dteDateOfBirth’ I can see straight away that it is a Date field and act on it accordingly.
This isn’t an exhaustive list just a list of the one I commonly use. Feel free to add your variations in the comments section.
Continue reading ‘Access Code Variable Prefixes That I Use’
I have played with the idea of maintaining a blog for a while now. I come across unique challenges every day and I think that it would be a great thing to record how I solved the problems so that I can refer to it in future if I need to and also hopefully other people could understand how I solved the problem and learn something from it too. Blogs have been around now for a while on the internet and they cover any topic from your family to marketing for a product.
Continue reading ‘WordPress For Business Bloggers Book’
