Interesting Excel Problem (ala Access style recordset)

[Bilbo_Baggins_Esq]

Hi All,

Are any of you Access Gurus handy in Excel?

I'm trying to solve a problem described here:
http://www.access-programmers.co.uk/forums/showthread.php?p=1298448#post1298448

The code and eventual functionality should/could/would likely end up being very similar to that which would be used in Access, but I cannot seem to make it work without an error.

Any help would be sincerely appreciated.

 

[Roku]

Hi
I'm not clear about what you want. Do you want Excel to connect to an Access query, or Access to connect to an Excel worksheet?
If it's the latter, you can use DoCmd.Transferspreadsheet action, if it's a one-time transfer (per task). If you want a dynamic link, please provide more details of your requirements.

 

[Bilbo_Baggins_Esq]

Connect Excel to an Excel workbook to pull a recordset from the second workbook.

http://www.access-programmers.co.uk/forums/showthread.php?t=254444

 

[Roku]

I'd use VBA to copy the worksheet you want to your target workbook, if that's what you really need. Here's some code which does that.

Private Sub getSheet()
Dim b1 As Workbook, b2 As Workbook
Dim s1 As Worksheet, s2 As Worksheet
Dim varSource As Variant
Set b1 = ThisWorkbook
Rem retrieve target file name from user
varSource = Application.GetOpenFilename("Excel files (*.xlsx),*.xlsx")
Rem determine response type
Select Case VarType(varSource)
  Case vbString
  Case vbBoolean
     MsgBox "Cancelled"
     Exit Sub
  Case Else
    MsgBox "Invalid"
    Exit Sub
End Select
Rem open the selected file
Set b2 = Workbooks.Open(Filename:=varSource, ReadOnly:=True, AddToMru:=False)
Rem remove old worksheet copies from destination
For Each s1 In b1.Worksheets
  If s1.Name = "CellData" Then
    Application.DisplayAlerts = False
    s1.Delete
    Application.DisplayAlerts = True
    Exit For
  End If
Next
Rem copy worksheet from source workbook
Set s2 = b2.Worksheets("CellData")
s2.Copy Before:=b1.Worksheets("Agents")
Rem close source workbook
b2.Close SaveChanges:=False
End Sub

Is there any reason you don't use direct references to the DatSource workbook from the DataDestination workbook? Something like:

=[DataSource.xlsx]CellData!$A$2

in a cell in DataDestination.

 

[Bilbo_Baggins_Esq]

Roku,

Thanks, but the none of this is a recordset.

I didn't want to get all distracted by long explanations but the reason I am not copy/pasting is it has to be done dozens of times.
I am actually remediating some existing code that was recorded by a novice user.
This code eventually bombs out becuase it has overloaded the Office Clipboard due to excessive copy/pasting.

Since there is no way to programatically empty the clipboard, I am attempting to achieve the end goal by pulling recordset from the various different files.

So, please, the ask is as described above.