Return cell values from a closed Excel workbook

Question

I pull data off of a database and manually enter it into an Excel report template.

I want with VBA code to lookup an exported closed Excel file (Test.xls) with data (example in cells B1:B21).

The data in the cells B1:B21 have spaces between each line. So vertically a column would be like the following

Data1  
Space  
Space  
Data2  
....

I want this with exception of the spaces to be put into the Excel report file, and displayed horizontally (A10:"Data1",B10:"Data2",C10:"data3"...) instead of vertically.

I cannot pull data directly from the database to the Excel template for security reasons.

I believe you'll need to open the xls to actually read from it, but if done in vba, the excel instance can be hidden, i.e. it won't appear to the user that another excel file is being opened. — Jaycal, Oct 15 '13 at 14:34
have a look in [**`here`**](http://stackoverflow.com/questions/18481330/2-dimensional-array-vba-from-cell-contents-in-excel/18481730#18481730) and learn a bit about the `Transpose` function :) — , Oct 15 '13 at 14:40
Open file, copy/paste column to new sheet, filter out blanks, copy/paste special transpose. — Alan Waage, Oct 15 '13 at 16:16

score 0 · Answer 1 · answered Oct 29 '13 at 11:36

Private Function GetValue(path, file, sheet, ref, v)

path = "C:\Documents and Settings\sdavis\Desktop\Index\XXX\Results"
file = "test.xls"
sheet = "Sheet1"
ref = "A1:R30"



 '   Retrieves a value from a closed workbook
Dim arg As String
Dim p As Integer
 '   Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
   GetValue = "File Not Found"
   Exit Function
End If








 '   Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Cells(v, 2).Address(, , xlR1C1)



  '   Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)


End Function

Sub TestGetValue()

'Declare
Dim v As Integer

'Starting Point
 v = 21

'File Location
path = "C:\Documents and Settings\sdavis\Desktop\Index\XXX\Results"
file = "test"
sheet = "Sheet1"

  Application.ScreenUpdating = False


    For C = 1 To 15

      a = Cells(5, C).Address
      Cells(5, C) = GetValue(path, file, sheet, a, v)
       v = v + 3
     Next C


Application.ScreenUpdating = True
End Sub

Can you please accept this answer by clicking the tick on the left? This marks the question as solved ;) — Federico Grandi, Dec 09 '18 at 18:59

Return cell values from a closed Excel workbook

1 Answers1