Databases
StackOverflow2013
Postsdbo
POCopy data from closed workbook based on variable user defined path
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCopy data from closed workbook based on variable user defined path
    text
    copied!<p>I have exhausted my search capabilities looking for a solution to this. Here is an outline of what I would like to do:</p> <ul> <li>User opens macro-enabled Excel file<br></li> <li>Immediate prompt displays for user to enter or select file path of desired workbooks. They will need to select two files, and the file names may not be consistent</li> <li>After entering the file locations, the first worksheet from the first file selection will be copied to the first worksheet of the macro-enabled workbook, and the first worksheet of the second file selection will be copied to the second worksheet of the macro-enabled workbook.</li> </ul> <p>I've come across some references to ADO, but I am really not familiar with that yet.</p> <p>Edit: I have found a code to import data from a closed file. I will need to tweak the range to return the variable results. </p> <pre><code> Private Function GetValue(path, file, sheet, ref) path = "C:\Users\crathbun\Desktop" file = "test.xlsx" sheet = "Sheet1" ref = "A1:R30" ' Retrieves a value from a closed workbook Dim arg As String ' Make sure the file exists If Right(path, 1) &lt;&gt; "\" Then path = path &amp; "\" If Dir(path &amp; file) = "" Then GetValue = "File Not Found" Exit Function End If ' Create the argument arg = "'" &amp; path &amp; "[" &amp; file &amp; "]" &amp; sheet &amp; "'!" &amp; _ Range(ref).Range("A1").Address(, , xlR1C1) ' Execute an XLM macro GetValue = ExecuteExcel4Macro(arg) End Function Sub TestGetValue() path = "C:\Users\crathbun\Desktop" file = "test" sheet = "Sheet1" Application.ScreenUpdating = False For r = 1 To 30 For C = 1 To 18 a = Cells(r, C).Address Cells(r, C) = GetValue(path, file, sheet, a) Next C Next r Application.ScreenUpdating = True End Sub </code></pre> <p>Now, I need a command button or userform that will immediately prompt the user to define a file path, and import the data from that file.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload