Extracting data from Excel.

Extracting data from Excel without opening the excel.

Page 1 of 1

2 Replies - 3254 Views - Last Post: 20 September 2009 - 05:15 AM Rate Topic: -----

#1 Irish_Dave  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 14-July 09

Extracting data from Excel.

Posted 03 September 2009 - 02:18 PM

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

Is This A Good Question/Topic? 0
  • +

Replies To: Extracting data from Excel.

#2 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 464
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: Extracting data from Excel.

Posted 04 September 2009 - 03:17 AM

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

Was This Post Helpful? 0
  • +
  • -

#3 Irish_Dave  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 14-July 09

Re: Extracting data from Excel.

Posted 20 September 2009 - 05:15 AM

Hi NoBrain, thanks you for your help.
Take care.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1