Copying Excel sheet data using VBA and ADO

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 more »

Copying Excel sheet data using VBA

When I started doing what I'm doing now for my employer, 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 more »

Excel – Check if a worksheet exists

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

Example of how to use:

Option Explicit

Sub DoingStuff()
   dim cBook As Workbook, _
      cSheet As Worksheet, _
      sName As String
   Set cBook = ActiveWorkbook
   sName = "SheetWithStuff"

   If doesSheetExist(cBook, sName) Then
      Set cSheet = cBook.Sheets(sName)
      ' Do stuff with sheet
   Else
      Set cSheet = cBook.Worksheets.Add
      cSheet.Name = sName
      ' Now do stuff with sheet
   End If
End Sub

I'm sure there's other ways, but this method is nice and fast. Returns True or False.