Kenneth has attended:
Excel VBA Intro Intermediate course
Read data from Excel spreadsheet
How do we read data from an Excel spreadsheet without opening the workbook?
RE: Read data from Excel spreadsheet
Hi Ken, thanks for posting your query. Open a new spreadsheet and write your name into cell A1. Then save that spreadsheet to c:\. Close it, and then open another new spreadsheet, Alt + F11 and create a new module with this code:
Sub ReadClosed()
Dim strPath As String
Dim strFile As String
Dim strInfoCell As String
Dim myvalue As String
strPath = "c:\"
strFile = "myspreadsheet.xls"
strInfoCell = "'" & strPath & "[" & strFile & "]Sheet1'!R1C1"
myvalue = ExecuteExcel4Macro(strInfoCell)
MsgBox myvalue
Sheets("Sheet1").Range("A1").Value = myvalue
End Sub
That should display a messagebox with the value from your closed spreadsheet and write it into cell A1 on your new sheet. I haven't tested this with vast quantities of data, but you should be able to cannibilise this code for what you want to do.
Hope this helps,
Anthony