I have used this below to get data from Access to Excel, is it possible to create a similar thing where it connects to another Excel Spreadsheet when opening another?
What I am wanting to do is. When I open Excel Report A that it connects to Report B and copies a range of data and then pastes back in Report A on a relevant sheet. Once it has done this then closes the connection between to the 2 Reports. I dont want to use the Link option as that gives me a permenant link.
Excel to Access code
Dim cnnDW As ADODB.Connection
Dim rsDW As ADODB.Recordset
Dim sQRY As String
Dim strDWFilePath, strCSVFilePath, strDestFilePath, strDestFileName As String
On Error GoTo Err:
strDWFilePath = "H:\NCHO\Housing Services\Data Warehouse\HSG Data Warehouse.mdb"
Set cnnDW = New ADODB.Connection
Set rsDW = New ADODB.Recordset
cnnDW.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDWFilePath & ";"
sQRY = "TRANSFORM Count([qryNoAccess(byAppt)].WRNumber) AS CountOfWRNumber " & _
Yes you can connect to Excel with ADO, this code seems to work
Dim strCon As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
strCon = "Provider=MSDASQL.1;" & _
"Data Source=TestExcelConnection;Extended" & _
"DBQ=H:\DBForum\TestDB.xls;" & _
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM [Sheet1$]", cn
Do Until rs.EOF
You can also specify a range name as the "Table" (including field names in the top row)
ie if the data you want is in the range named 'TableRange' then use this
rs.Open "SELECT * FROM TableRange", cn
You could also open the required workbook and copy anything you like into the 'current' workbook and close it afterwords.
If you turn ScreenUpdating off before and on again afterwards, you would be non the wiser.
If you do this, set a reference (object variable) to the 'current' workbook before opening the second book as this becomed the 'current' book when opened.
This is my normal way with spreadsheet, mainly because they usually (always!) have different name or are in different places or both.