[Irish_Dave]

I am trying to extract some data from an excel spreadsheet which is in a closed book and place the data extracted in to an excel spreadsheet called Contracts which is open.

When the excel spreadsheet called Logsheet is opened it will take the data from the closed excel workbook and place the data into the sheet called "Contracts" which is in the open excel spreadsheet called "Logsheet"

I have attempted to solve thos problem but keep getting a run time error 1004 some object reference problem.


The following code in place in a module 3 of the logsheet.


Public Sub PullInSheet1()


'Clear sheet ready for new data

Worksheets("Contracts").UsedRange.Clear

'Reference the UsedRange Address of Sheet1 in the closed Workbook.

Worksheets("Contracts").Cells(1, 1) = "= 'C:\My Documents\LOG\" & "[ContractA.xls]Sheet2'!RC" 'Edited this one from sheet 1, 1 to 2,2

'Pass the area Address to a String

AreaAddress = Worksheets("Contracts").Cells(1, 1)

With Worksheets("Contracts").Range(AreaAddress)

'If the cell in Sheet1 of the closed workbook is not empty the pull in it's content, else put in an Error.

.FormulaR1C1 = "=IF('C:\My Documents\OzGrid\LOG\" & "[ContractA.xls]Sheet1'!RC="""",NA(),'C:\My Documents\" & "LOG\[ContractA.xls]Sheet1'!RC)"

'Delete all Error cells

On Error Resume Next

.SpecialCells(xlCellTypeFormulas, xlErrors).Clear

On Error GoTo 0


End With


End Sub



Not sure why this is not working.

If anyone can help I would be very greatful.

All the best Irish_Dave

[raziel_]

hello there was a way that you can extract data from excell using adodb.recordset

see this
http://www.daniweb.c...read51162.html#

i hope this will help you

This post has been edited by NoBrain: 04 September 2009 - 03:18 AM

[Irish_Dave]

Hi NoBrain, thanks you for your help.
Take care.