Excel Data Transfer via SQL Call

I was able to copy data from source to target without opening the source file by using the below code in my sub.  It works okay, but I am still trying to figure out how to achieve that by SQL calls.

ThisWorkbook.Worksheets("Sheet1").Range("NewRange").Value = "='C:\Users\rerden\Desktop\Access and Excel Transitioning\Excel SQL Call Test\SQLDataTransfer\[SQL_TestA.xlsm]Sheet1'!MyRange"

Background:
To more realisctically illustrate my problem I have attached 2 new files. SampleData.xlsm (source file) and Flipper_EE.xlsm (target file).  Flipper file has 3 tabs, Menu, Log and Retrieve.  On Menu tab Start button opens the file in location entered on PathFolder range of the same worksheet.  

Then it pulls from the specified fields of the SampleData file and populates the 3rd row of the Retrieve tab. If there were multiple files in the source folder then it would populate the subsequent rows for each file in that folder. It also logs the source data file name and location used for this process on the Log tab.  

The sub procedure Extract Tasks in Module1 first populates top two rows with headers and code down to Ln 261 in this sub is used only to populate the top two rows with appropriate headings. Code blocks following are to actually do the source to target data transfer.

My Goal:
The essence of what I am trying to achieve is applicable to code block starting with Ln 269 within Extract Tasks sub of Module1.  VBA in this block opens up the SampelData file then extracts from the specified cells or ranges then continues to the next block once all blocks are completed it closes the source file and opens the next file in the folder and keeps repeating this process. What I want is to get data without opening source files.  I have been trying to achieve this by using SQL calls but could not figure it out how.  I tried another method described on my May 6 post here but it creates links and I would like to avoid that.  

There is a comment that I put for the expert who decided to work on problem for the code block starting with Ln 269.  If SQL call process or ADODB type or a similar process of data extraction without opening the source file can be applied to this code block under Development comment I would consider the solution as good and could applpy it to the rest of the blocks.  This code block simply populates the rows on Flipper (target file) right below the headers for Development Name, Primary Address, City, Zip Code and County by pulling data from SampelData file's Summary tab's cells I5 to I9.  

Note:
If any tab shows protected, the password is "MHFApp14".  Also, I keep the target file on a different location than the source file to make the process work.  I am also open to other methods where I can do the extraction without opening the source files other than SQL calls but thought that would be a more robust method.

Thank you!
C--Users-rerden-Desktop-Access-and-Excel
C--Users-rerden-Desktop-Access-and-Excel

Why don't you create a local [Retrieve] worksheet in each of the source files to match the required layout in the target file, & set each of the cells in one row to point to the various locations across the rest of the worksheets in the respective source file?

That way, you can simply copy'n'paste data from the source file [Retrieve] worksheet to the target file [Retrieve] worksheet either using Visual Basic for Applications statements, or ADODB SQL syntax.

There are various business reasons that created a need for a separate and stand alone data extraction tool in this case the target file or flipper file as I referred t it. Explanation of those business rules to justify the exisiting setup is certainly outside of the scope of this question and I need to operate within the confines of those rules for sure.  Due to those set of business requirements the solution type that you described in your comment is not a viable option at all.  Do you think you can tackle on this problem in the way that I tried to describe?  Thank you!

Following up on my previous comment - You finished your entence with "That way, you can simply copy'n'paste data from the source file [Retrieve] worksheet to the target file [Retrieve] worksheet either using Visual Basic for Applications statements, or ADODB SQL syntax."  This is exactly what I am asking, how to do it with ADODB SQL syntax...

You can certainly do what you are asking using ADODB SQL statements/recordsets, but due to the nature of the data being scattered throughout the (example) source file, the speed improvement of the execution of the code is not likely to be great.

The workbook is still required to be opened, whether you use the ADODB method, or not, so there is no saving on the time taken to access network-hosted files.

The fact that the source data is located throughout many of the worksheets within the source file, & the amount of cells you required to extract to the [Retrieve] worksheet in the target file, is the main issue.

It is true that your existing Visual Basic for Applications code could be written differently to provide faster performance, but the introduction of ADODB/SQL-based statements, & opening/closing the associated recordsets, may even make the process slower.

Could the source workbook files not be stored locally whilst the data extraction was taking place?
You mentioned storage on a network originally, but in your later example you used the location: "C:\Users\rerden\Desktop\Access and Excel Transitioning\Excel SQL Call Test\SQLDataTransfer\...".

My suggestion about ensuring a local worksheet exists that pre-formats the data so that a single SQL statement can be used, or a simple copy'n'paste of data from one worksheet (in the source workbook) to another worksheet (within the target workbook), is by far the easiest solution.

If the pre-formatted [Retrieve] worksheet within each source workbook is (very)hidden, will this bypass the business rules/requirements you mentioned?

I used this method ("C:\Users\rerden\Desktop\Access and Excel Transitioning\Excel SQL Call Test\SQLDataTransfer\...".) and achieved my goal of getting data from the source file without opening it.  But I like to avoid from using this method as it creates links and for sure there will be lots of links to be maintained.  From my experience if I could avoid lots of links I certainly will.  So I ruled out that option.  By the way this is my test location and not to be used for the production.

Speed actually is the secondary goal to me and if not enough speed gains are made that is ok too. My priority here is to get data without opening the source file.  Primary reason for doing so is that source files create Active X related errors on file open and if and when that happens the transfer process freezes up, they are unpredictable.  If I can get data from those source files without opening them, those ugly active X errors due to controls on source file tabs will be avoided all together.

You are saying that files need to be opened whether I use SQL calls or not in the process.  I am sure this works without opening the source files, actually I do have a sample process doing it, not doing it right in my case but it proved to me that extraction is possible without opening source data file.  If you like I can send you sample file for proof but I dont want to create a file clutter.  I am pretty sure that with SQL ADODB statements and records sets extraction is possible withoput opening the source file.

Going back to your recommended method which is having a hidden pre-formatted Retrieve tab embedded on each source file which gets populated from the other worksheets then making one copy/paste procedure via SQL call to the target file  or by employing the current method.  I would consider this method on the side to come up with an alternative data transfer option and to prove that it can be an alternative to myself.  But I must stick with the current data extraction setup at least for the time being.

So to sum it up here my main benefit would be to extract data without opening the source files hence escaping those active x errors.  Also, I do have to deliver this solution if I can within the same file setup and data extraction routine and I don't have the option of offering a totally different method as you mentioned although I certainly see your point with that.  I understand that with SQL calls VBA still need to go through a widely scattered data on multiple tabs and ranges but as long as it is done without opening the source files it would be okay.


Thank you

Ah.... I appreciate what you mean by "not opening" the workbook files now; to avoid the ActiveX control errors is your primary goal (rather than it being a secondary advantage as hinted at within the last sentence of the opening question text).  I concentrated on what seemed to be your main desire; a speed improvement.

The file still needs to be opened to use ADODB-based extraction, just not in the conventional sense via the MS-Excel Graphical User Interface (so none of the other workbook components need to be loaded); just the stream of data via the Data Objects component layer.

---

OK... a lot of text has been exchanged & we are not really any nearer to understanding the actual problem(s) you have encountered so far.

If you have sample code that already does what you require, what is the issue, or issues, you are encountering applying the method to your existing "Flipper" workbook code?

Thanks for focusing on the definition and clarification of the "File Open" concept.  I meant opening an Excel workbook file in its conventional sense i.e. loading all the components, and UI then do the extraction.  This is what I want to avoid so I believe we are on the same page with that.  As you described the source file should be opened only to pull data via data objects component layer.  

Correction on my statement on "sample code", When I referred to as "I have the sample code" works similar to the one that I mentioned before, both use the full path to extract data without opening the file. Former one (May 6th post)) creates a link and this one does not cerate a link although not working completely.  So you can safely ignore that statement about that sample code. I thought it was ADO SQL call code sample but it was not...my bad sorry.  I tried to find several ADODB SQL VBA code samples to do the extraction but none worked or I could not get them worked.  

In an attemtp to simplify my question I will not get into what I have tried so far, what worked or what failed and will jump right into what exactly I need to accomplish.

So going back to your question of "what is the issue?"

If you look at my post from May 8th with two files attached, I was trying to populate the Retrieve tab of the target file (Flipper) rows using the source file and the code you see there is working ok but it does the job by opening each source file in its conventional sense by that all the components and the user interface are being loaded.  

Could you populate for example the first row after the headers (3rd row) on the Retrieve tab of the target file for the first 5 columns by not opening the source file (not opening in the conventional sense)?  

If that could be done with SQL calls using ADODB record sets it is good or any other method that you would recommend would also be good.

Attaching those two files again one is the target file(flipper) and the other one is the source file.


Thank you for your patience on this problem.
C--Users-rerden-Desktop-Access-and-Excel
C--Users-rerden-Desktop-Access-and-Excel

To extract the contents of a cell from a workbook without opening it you can use a routine such as this:

Private Function vntGet_External_Range(ByVal strPath As String, _
                                       ByVal strFilename As String, _
                                       ByVal strWorksheet As String, _
                                       ByVal strRange As String) As Variant
                          
  Dim vntReturn                                         As Variant
  
  On Error GoTo Err_vntGet_External_Range
  
  If Right$(strPath, 1) <> Application.PathSeparator Then
     strPath = strPath & Application.PathSeparator
  End If
    
  vntReturn = ExecuteExcel4Macro("'" & strPath & _
                                 "[" & strFilename & "]" & _
                                 strWorksheet & _
                                 "'!" & Range(strRange).Range("A1").Address(, , xlR1C1))
    
Exit_vntGet_External_Range:

  On Error Resume Next
  
  vntGet_External_Range = vntReturn
  
  Exit Function
  
Err_vntGet_External_Range:
  
  vntReturn = "Error #" & CStr(Err.Number) & " - " & Err.Description
  
  On Error Resume Next
  
  Resume Exit_vntGet_External_Range
  
End Function

Select allOpen in new window

Assuming that this code is placed in one workbook, & an additional workbook exists called "TestFile1.xls" that is stored within the same folder as the first workbook, then the following statements will extract data from the closed workbook:

Public Sub Test()

  Debug.Print "TestFile1.xls Sheet1![A1]:", vntGet_External_Range(ThisWorkbook.Path, "TestFile1.xls", "Sheet1", "A1")
  Debug.Print "TestFile1.xls Sheet2![B2]:", vntGet_External_Range(ThisWorkbook.Path, "TestFile1.xls", "Sheet2", "B2")
  
End Sub

Select allOpen in new window

It is assumed that "TestFile1.xls" has (at least) two worksheets [Sheet1], & [Sheet2].

On the code that you provided the Public Sub Test () procedure using vntGet_External_Range() function is forcing me to use the browser to find the path and click on the source file manually.  Although the source file is not being opened as I wanted to extract data this is still not a complete solution.  

VBA needs to find the source (SampleData) file in the given path provided on the PathFolder range of the Menu tab residing on the target file (Flipper) without any user intervention then populate rows on Retrieve tab per file in that location without opening the source file.  

Is it possible that  you could implement your VBA solution on the sample target file that I provided and make it work the way I described on that file? Must avoid manual file browse and file select in the process.  So, when the Start button is clicked on Menu tab Sub OneDir() procedure runs, during this run Sub Extract Tasks() procedure gets activated and populates the Retrieve tab rows which is currently how it works. What I need here is that this process should do the job w/o file open.

If you could provide your solution to populate the first row after the headers for the first 5 columns on the Retrieve tab of the target file without opening the source file I can go ahead and implement that solution for the rest of the columns.

On Target file (Flipper) under Sub Extract Tasks() procedure you will see the below code which populates the first 5 columns. Is there any way for you to leverage this code or similar and implement your code to accomplish what I need?


This code block starts at Ln 269 in Sub Extract Tasks () located in Module1.

ThisWorkbook.Worksheets("Retrieve").Cells(RL, 1).Value = Workbooks(DataFile).Worksheets("Summary").Range("I5").Value 'Development Name
ThisWorkbook.Worksheets("Retrieve").Cells(RL, 2).Value = Workbooks(DataFile).Worksheets("Summary").Range("I6").Value 'Primary Address
ThisWorkbook.Worksheets("Retrieve").Cells(RL, 3).Value = Workbooks(DataFile).Worksheets("Summary").Range("I7").Value 'City
ThisWorkbook.Worksheets("Retrieve").Cells(RL, 4).Value = Workbooks(DataFile).Worksheets("Summary").Range("I8").Value 'Zip Code
ThisWorkbook.Worksheets("Retrieve").Cells(RL, 5).Value = Workbooks(DataFile).Worksheets("Summary").Range("I9").Value  'County


Thank you

"On the code that you provided the Public Sub Test () procedure using vntGet_External_Range() function is forcing me to use the browser to find the path and click on the source file manually."

Rather than "the browser", do you mean the File Open dialog box in MS-Excel, or MS-Windows Explorer?

In any case, if you are seeing anything other than the value returned, please check the folder & filename you are using, & that the workbook file is in the required location.

Sorry it took me while to respond to your post, it's been super hectic here.  Standing corrected...Yes, I did mean the File Open dialog box.  I checked my process again and files are in their respective folders. The code works, I can see that in the Imeediate window the results.  But again I should not be responding to File Open prompt then pick and choose a file selecting its location.  The solution that modifies the Extraction Tasks procedure on my sample file for the first five columns to be populated on target file would be much appreciated.  Or if you prefer your own working sample file as a proof of concept doing the same routine would also be ok.

Fanpages...are you still interested in helping since the last week?

Trusxlsol,

To articulate what you are trying to do here in my words so that i understand better is that you have this data in SQL and now you want to get this data from SQL to this excel file from a different excel file without opening the excel file that you want to populate this data to?

Also any particular reason that you have that you don't want to open the main excel file which you want to populate this data?? In additional what i understand from SQL you are fetching this external data by get external data option.

Saurabh...

I will get back to you on your questions soon after I re-structure my question with a new sample..Thank you.

I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.

I object to removing this question because:

a) I advised that the method requested would be too cumbersome to implement due to the way in which the data was structured,

b) I suggested alternate ways in which the data could be structured to make implementation of the ADODB/SQL statement-based method easier (although the question asker rejected these due to other constraints),

c) I offered another approach that was not fully investigated, although in my local testing did produce the outcome anticipated.


The onus is upon the question asker to return to respond to another Experts query.

Simply removing all record of this discussion so far will dismiss the advice as worthless.

I believe there is value to retaining the text; not only to this question, but potentially similar questions in the future, not necessarily from the same question asker.

I have not had any chance to even look at EE site due to several circumstances during the last 3 weeks my apologies.  I do understand that after certain time elapses the unresolved questions are put in deletion bucket.  My response to that is to keep this question in archive as a last resort instead of deletion if possible and I like to have an access to this question and solutions offered in the future. I believe there might be a referential value in it although I have not accepted any solutions so far.  I agree with Fanpages' argument above.  I will make another attempt but this time with a far more scaled down and simple data sets and example files to simulate what I was trying to achieve and I will do so during the next week.  If fanpages has still interest that would be nice if not hopefully another expert can help.  At this point my position on this matter is to keep the question as is and active and will do my best to re-iterate this matter soon.  Thank you!

Thanks eenookami.

I have been trying to attach my sample files on my work desktop but your site has been acting up in a way that once I click on Attach File option warning box pops up and the close or ok options are not visible as the dialog box positioning on the screen is way off.  Have played with my resolution etc. no help.  I will post my comment along with the sample files from my home computer this evening hope it workls there.  So please bear with me until tonight, if still having problems will put a followup comment here to that effect.

Until I add my sample files below is my question.  Expert may choose to create his/her sample files fitting to the description and start working on this question.  There you go...

Goal:
I have attached 3 sample files to illustrate what I have been trying to achieve with a very simple data set on the Source File.  The goal is to extract data from the source file and place that data in designated cells in the Target file.  There is also a Control file that I use, that file should only store the VBA code to manipulate the source and target files and the extraction process.

Constraints:
The method that I need to employ to achieve this goal is to use SQL calls in Excel 2010.  Need to create record set and using ADO DB method something similar to below code to give an idea.  This is just a piece of the begining code that I have placed here to elaborate on my illsutration only.

Public Function conn(Optional strFile As String = "", Optional strHeader As String = "NO") As ADODB.Connection
   
    Dim mConn As New ADODB.Connection
   
    On Error GoTo errHandler
   
       mConn.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile & ";Extended Properties=""Excel 12.0 Xml;HDR=" & strHeader & ";IMEX=1"";")
       
    Set conn = mConn

...

Process Flow:

VBA in the Control File first opens up the Target File which is like a template once the user clicks on the Transfer Data button. Target File should remain open during the process.  Then it extracts data from the Source File in designated folder - without opening the Target File and this is a key in the process. Then it places the value in the Source File.  Since I am avoiding to open the file first there should not be file copy/paste at all.  No other method should be employed to extract the source values such as linking files or similar.  The only method that I would like to use would be ADO DB and SQL calls.  Once the transfer is completed VBA names the file as "Report"..

Attached are the sample files to support the above question.
ControlFile.xlsx
TargetFile.xlsx
SourceFile.xlsx

Is my above question making enough sense to work with?  Please let me know if I need any further elaboration.

I've requested that this question be deleted for the following reason:

The question has either no comments or not enough useful information to be called an "answer".

At this point I am indifferent about how  you will administratively move about my question at all. On the other hand IF that makes any difference I would like to make an overall comment. This question for some reason never got resolved.  One attempt has been made which was not even close to what I was trying to obtain and when indicated that there was no more follow up.  There was only one other expert joined once and I re-structured my question and posted again.  Never heard from that expert later.  What I asked is very legitimate question, it is in use and it works.  Extracting a record from an Excel file using SQL calls after creating a record set is certainly a function that Excel can handle. We are using it here at work, I was trying to understand with a simplified example  in regards to how to come up with a sample VBA code to do just that myself.  Opening an Excel file and extracting data from it and putting that data into another Excel file. I am only wondering why Experts Exchange failed to resolve this question.  My question was very carefully setup and supported with sample data files, and later re-structured it and attached very simple files to illustrate what I was trying to achieve.  I wish I could have a satisfying explanation about what just happened that in the experts community there was not a single high ranking Excel expert who could be able to take a stab at my question.  
Thank you for no solution Experts!

trusxlsol,

Somehow i skipped this question..and now let me work with you to ensure this get resolved...A

So i'm rephrasing this in my language so that i understand this is what you are trying to do...

1. You have a Control file..Basically from the file where you want to run the macro and run everything...

2. Now From the control file..you want to run a macro or a query and get the data in the source file from Sql Server

3. Now once you get the data from sql server..You want to copy this data from source file to target file?

Now i have couple of questions to this...

1. Can you provide me your sql query which you are running to populate this data?

2. You connect to sql using windows authentication or sql authentication?

3. Is their any particular reason you have source file in place? Can i populate this data directly in your target file..?? Because i dont see a need for a source file out here since i can get this data directly in the target file...

Saurabh...

"ID: 40789955"...
---
Sorry it took me while to respond to your post, it's been super hectic here.  Standing corrected...Yes, I did mean the File Open dialog box.  I checked my process again and files are in their respective folders. The code works, I can see that in the Imeediate window the results.  But again I should not be responding to File Open prompt then pick and choose a file selecting its location.  The solution that modifies the Extraction Tasks procedure on my sample file for the first five columns to be populated on target file would be much appreciated.  Or if you prefer your own working sample file as a proof of concept doing the same routine would also be ok.
---

Sorry, I just did not have the time to write your project for you beyond my previously provided code (that you confirmed did work).

I think there is a lot of work here to determine what is happening with your own runtime environment with respect to seeing a "File Open" dialog, when one should not be shown at all with the code provided.

Responding to both Saurabh Singh Teotia and fanpages,

Teotia - You are under the assumtion that I am using SQL Server. This is incorrect.  All the work is to be completed in Excel and nothing else is used. I never mentioned anywhere in my thread SQL Server at all. There is also not a SQL statement written for this project. Just trying to make sure that we are on the same page first.

Fanpages -  You surely don't think that I was expecting you to write my project for me. I do appreciate the code that you sent me but as I explained for the reasons before that it was not what I was looking for, not that it was not working but not working the way I was hoping.

First of all if there is any motivation here to take a stab at my question please see my July 2nd post. I attached 3 files there with only a few data points and explained what I am trying to do.  

Expanding on my July 2nd Question:

My high level goal is using the Control file to do data extraction from the source file and place the extracted data set into the target file's designated location.  Control file should hold only VBA code to run the whole action and nothing else in it.  

The way I need to accomplish this goal is through making a SQL call in Excel.  I added some code piece on July 2nd post just to give an idea.  My motivation behind using SQL call is to bypass the option of VBA physically opening the source file then copying/pasting data from soource to target then closing the source file and repeat this process for the rest of the files in the folder.  Instead, using SQL calls VBA can extract data from the source file without even opening the file as regular file open command does.

To be able to use SQL call I tried to create a record set as a variable (rs) then leveraging the ADODB connection string but failed to get it work.

The control file can be located anywhere but source and target files for the sake of simplicity are to be located in the same folder. Once the Transfer Data button is clicked on the control file target file should be opened up and remained open while data extraction is being done from the source file and placed into target file, target remains open as data transfer process goes through all the source files in the folder.

I stated earlier as fanpages also commented on it that we do have a process at work based on SQL calls. I tried to implement it into a much simpler file setup as it is very highly convoluted and complex. Unfortunately I failed to use it as a guide and simulate in my much simpler platform as a proof of concept hence asking for help.  

Please let me know if my explanation is not clear enough or has missing components or if you need a different or better sample setup.  Again as usual I do appreciate your help.

Thank you

Okay to get it more aligned this is what you are trying to do..you have file-3 from which you want to use as your control panel from where you want to execute this macro to go to file-1 and without opening that file-1 using the adodb method copy data from file-1 to file-2

Is this what you are trying to do??

Saurabh...

Yes to illustrate it better,

1. Control File
2. Source File
3. Target File

Process:
Launch control file (1)
Run macro on 1 (Transfer Data button)  - it opens up 3 then extract data from 2 and place it into 3 (without opening 2) using ADODB (SQL call method).

Okay..Here is the code which will do what you are looking for..Now when you get the data you actually shift column to rows and rows to columns so i'm pasting this data as it's to sheet2 and then switching it over..Again i have assumed my sourcefile and target file in the same folder you can change it basis of your need..

Sub Button1_Click()


    Dim cn As New ADODB.Connection
    Dim rec As New ADODB.Recordset
    Dim mySQL As String, str As String
    Dim xpath As String
    Dim i As Long
    Dim wb As Workbook, wb1 As Workbook
    Dim ws As Worksheet, ws1 As Worksheet
    Dim lrow As Long, lcol As Long

    Set wb = ThisWorkbook

    xpath = "C:\Users\youridhere\Downloads\"

    Set wb1 = Workbooks.Open(xpath & "TargetFile.xlsx")

    Set ws1 = Sheets("Sheet2")
    Set ws = Sheets("Sheet1")

    str = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
          "Data Source=" & xpath & "SourceFile.xlsx;" & _
          "Extended Properties=Excel 12.0"
    cn.Open str

    mySQL = "SELECT * FROM [Sheet1$a1:f6]"


    rec.Open mySQL, cn, adOpenUnspecified, adLockUnspecified

    ws1.Range("A2").CopyFromRecordset rec

    lrow = ws1.Cells(Cells.Rows.Count, "A").End(xlUp).Row
    lcol = ws1.Cells(2, Cells.Columns.Count).End(xlToLeft).Column

    ws1.Range("B2:" & Cells(lrow, lcol).Address).Copy
    ws.Range("B2").PasteSpecial xlPasteValues, Transpose:=True
    ws1.Cells.Clear

    rec.Close
    cn.Close
    Set rec = Nothing

    Set cn = Nothing
End Sub

Select allOpen in new window

This does what you are looking for..

Saurabh...

I did run your code after attaching to the data transfer button on ControlFile and got compile error - "user defined type not defined" was the error description.  Attached the ControlFile here for you to review with your code in it. This is certainly the way that I was trying to get the data extraction done so we are good with that. Could you please take a look at that file and make sure that it works on your end then send me the working copy if possible?  Not sure what went wrong. I changed the xpath for my directory/file location. Thank you for your effort on my question.
C--Users-rerden-Desktop-Misc-EE-Excel-SQ

Great!  It works and problem resolved.  Thank you!

Thank you! Good solution.

You are welcome..Always Happy to Help.. :-)

Saurabh...