All You Should Read

So what do you do if you need to copy data from an Excel workbook that is maintained by other people? And is, therefore, located somewhere else? And you do not have dedicated access to it? And you can not run an unobserved, automated workbook in that location?

Other than manually opening the workbook and copy/pasting the data (time consuming), there really is no other solution than making a copy and using that copy for your automation.

Fortunately, database tools come to the rescue; the very way that Excel works is similar to that of an actual database: information is stored in rows and columns, and each worksheet can be considered a table.

Read the rest of this entry »

Post has been updated
I’d turned rows and columns around initially… oops.

---

When I started doing what I’m doing now for my boss, I inherited a large group of data collection files that pull information from one application or another into an Excel sheet, format that information, and save it into a simple CSV file for database import.

The method that was used, however, utilized Excel’s most basic functions: Copy and PasteSpecialValues. A lot of these data collecting workbooks run all by themselves, mostly unmonitored, on a fixed schedule. A problem arises when a second workbook opens to do its job, while the first is still busy copy/pasting.

The result? Inconsistent (or even missing) data.

Another downside of using coded copy/paste actions is that, when you run the workbook on your own system, you pretty much have to sit back and wait for things to finish — a lot of office work consists of copy/pasting information. Imagine working on a document when you have Excel busily using the clipboard for minutes on end… not a pretty sight.

Read the rest of this entry »

So you’re trying to read from or write to (or create a) worksheet… but does it even exist?

Function doesSheetExist(wBook As workbook, sheetName As string) As Boolean
   Dim wSheet As worksheet
   doesSheetExist = False
   For Each wSheet In wBook.Worksheets
      If wSheet.Name = sheetName Then
         doesSheetExist = True
         Exit For
      End If
   Next
End Function

Read the rest of this entry »