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