Excel VBA: Automatic opening of external files and other neat tricks

Suppose you have a workflow in Excel that involves manual copy-and-paste of data from one spreadsheet (A) into another spreadsheet (B), and you want to automate it.

In some cases, you might make use of data sources and automatic linking between files, but there are many cases where this would be undesirable. Either the automatic update of links makes your process undesirably slow, or you need those intermediate values unchanged for reporting/auditing purposes, or the links update is just too buggy and unreliable (as are, alas, many automatic features in the MS Office suite), whatever the reason, sometimes you are better off with the "plain old-school" VBA macro approach, approximately,

1. Open the external spreadsheet A
2. Find the source data
3. Copy the data into your destination spreadsheet B

To do this gracefully, one must think through several scenarios though. What if the source spreadsheet is already open - should we re-open it (possibly discarding unsaved changes), or should we reuse the already opened sheet? What do we do after the data copying is done - should we force-close the source sheet or should we keep it? 

After giving these questions some thought, I have created a short VBA code snippet, and after having re-used it about a dozen times over the course of less than one month, I am putting it here for reference. Here goes:

Set targetWB = Application.ActiveWorkbook
TargetPath = targetWB.Path & "\"
SourcePath = "..\otherfolder" 'Relative to target workbook 
SourceName = "somename.xlsx" 'Can be any calculation to determine name

alreadyOpen = False
For Each thisWB In Workbooks
  If thisWB.Name = SourceName Then
   alreadyOpen = True: Set sourceWB = thisWB
  End If
Next thisWB
On Error GoTo ERRORHANDLER ' to gracefully handle "File not found" scenarios
 If Not alreadyOpen Then Set sourceWB = Workbooks.Open(Filename:=TargetPath & SourcePath & SourceName, ReadOnly:=True)
On Error GoTo 0

' *** Data copying code goes here *** 

If Not alreadyOpen Then sourceWB.Close SaveChanges:=False
Set sourceWB = Nothing: Set targetWB = Nothing ' garbage collection

Note a few touches here: 

• The code reuses the already opened spreadsheet if it is already open (pardon the tautology), and opens it otherwise. This makes the code both faster for debugging (no repeated open/close - the source workbook can be large!) and suitable for automated production workflows. The only downside here is that you need to separate the actual name of the spreadsheet, and a path to it; however if you only have the full path, you can easily work around it with a one-liner like so:

  SourceName = FullPathName: While InStr(SourceName,"\") > 0: SourceName = Right(SourceName,Len(SourceName)-InStr(SourceName,"\")): Wend

  using FullPathName instead of TargetPath & SourcePath & SourceName in Workbooks.Open(...).
• The code automatically closes the source spreadsheet only if it had to open it. This way, no spurious workbooks are left open in a production workflow.
• The files are opened read-only and closed without saving changes. This prevents unwanted queries that can otherwise pop up if the file happens to be opened by another user, or if the data copying process ends up messing up the source file (which, depending on how complicated the data processing routine is, is quite likely to happen). These queries are annoying whilst debugging and totally disruptive in a production workflow; last but not least we are preventing unwanted modification of the source file.

BONUS: Here is the routine I commonly use for the actual data copying. I find it preferable to using selection / clipboard / autofilter operations because it involves less GUI interaction and therefore is more robust (and, with Application.ScrenUpdating=True, can be faster!)

targetWS = "Analysis" : sourceWS = "Data" 'use any names 
targetStartAt = "A2": sourceStartAt = "A2" 
  'feel free to use named ranges here
  'or to have your code determine the locations based on search criteria
Set here = sourceWB.Worksheets(sourceWS).Range(sourceStartAt)
Set there = targetWB.Worksheets(targerWS).Range(targetStartAt)
While Len(here.value) > 0 
 If Not IsError(here.Value) Then
  If here.Value = "GOOD" Then ' put whatever condition to validate a source line to determine if it needs copying
   ' example of data copying, edit as your task requires
   there.Value = here.Value
   there.Offset(0,1).Value = Left(here.Offset(0,1).Value,8)
   For i = 4 to 12 
    there.Offset(0,i-2).Value = here.Offset(0,i).Value
   Next i
   Set there = there.Offset(1,0)
  End If
 End If
 Set here = here.Offset(1,0)
Wend