Excel Data Connection to Closed Excel File

[dgrimm]

I am attempting to change some code for retrieving data from a closed workbook.

Currently I have the following code.

Sub COPY_PASTE_DATA()

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual


MsgBox "It will take a couple of minutes for complete update to be completed.", vbInformation, "Update"

Sheets("DateSelection").Visible = True

For Each wssheet In ActiveWorkbook.Worksheets
If wssheet.Name <> "DateSelection" Then
If wssheet.Visible = True Then
wssheet.Visible = False
End If
Else: wssheet.Visible = True
End If
Next wssheet

Sheet3.Visible = True
Sheets("ARES").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents


'Go get data and pastse on ARES tab
Dim ActWbk As Excel.Workbook
Dim DataWbk As Excel.Workbook

strconSource = "\\Ovpfs1.swissre.com\Legacy\corp\hea\Apps\STOP LOSS\jpo\ultimatePA\"
strwbName = Sheet14.Range("A6").Value & "\" & Sheet14.Range("A6").Value & "-" & Sheet11.Range("J6").Value & "\SNAP_SHOT-" & Sheet14.Range("A6").Value & "-" & Sheet14.Range("B6").Value & "-" & Sheet14.Range("C6").Value & ".xlsm"

Set ActWbk = ThisWorkbook
Set DataWbk = Workbooks.Open(strconSource & strwbName, , , , , , True)



DataWbk.Activate
Sheets("ARES").Select
Columns("A:BK").Select
Selection.Copy

ActWbk.Activate
Sheets("ARES").Select
Range("A1").Activate
Columns("A:BK").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

DataWbk.Activate
ActiveWindow.Close

ActWbk.Activate

Call IDCreation



Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic



End Sub

But what I would like to do is be able to retrieve the data without opening the closed file.

I ran the connection tool and found something on the Internet to get the following code.

Dim Conn As String
Conn = "Provider=Microsoft.ACE.OLEDB.12.0;Password="""";"
Conn = Conn & "User ID=Admin;Data Source=J:\STOP LOSS\jpo\ultimatePA\2011\2011-05\SNAP_SHOT-"
Conn = Conn & Sheet14.Range("A6").Value & "-" & Sheet14.Range("B6").Value & "-" & Sheet14.Range("C6").Value & ".xlsm;"
Conn = Conn & "Mode=Share Deny Write;"
Conn = Conn & "Extended Properties=""HDR=YES;"";"
Conn = Conn & "Jet OLEDB:System database="""";"
Conn = Conn & "Jet OLEDB:Registry Path="""";"
Conn = Conn & "Jet OLEDB:Database Password="""";"
Conn = Conn & "Jet OLEDB:Engine Type=37;"
Conn = Conn & "Jet OLEDB:Database Locking Mode=0;"
Conn = Conn & "Jet OLEDB:Global Partial Bulk Ops=2;"
Conn = Conn & "Jet OLEDB:Global Bulk Transactions=1;"
Conn = Conn & "Jet OLEDB:New Database Password="""";"
Conn = Conn & "Jet OLEDB:Create System Database=False;"
Conn = Conn & "Jet OLEDB:Encrypt Database=False;"
Conn = Conn & "Jet OLEDB:Don't Copy Locale on Compact=False;"
Conn = Conn & "Jet OLEDB:Compact Without Replica Repair=False;"
Conn = Conn & "Jet OLEDB:SFP=False;"
Conn = Conn & "Jet OLEDB:Support Complex Data=False"


The problem that I have is where do I put it into the code, what do I take out of the code and how do I still get the table (ARES) from the closed workbook and place it where I need to in the current workbook (ARES worksheet). After that I can rewrite my other code to fit this code. It is simple code that I create IDs from this table that I would like to retrieve.

The above code works but takes some time and even with the screen updating set to false it still flashes around the different files.

Thank you

Dave