Old code not working in 2010

**kevincoxshall** · 04-02-2014, 05:17 PM

Old version question here,

I can get this code to work in an older excel but I cannot get it to work in excel 2010.
Does anyone have any idea why it doesn't work?
The code pulls data from a closed file without opening it.

Sub DataFromClosedBook()
Dim mydata1 As String
mydata1 = "=c:\extract data\[r.xls]Sheet1'!$A$1:$A$10"
With ThisWorkbook.Worksheets(1).Range("a1:a10")
.Formula = mydata1
End With
End Sub

Any hints welcome

Kevin

**6StringJazzer** · 04-02-2014, 05:59 PM

I don't think you can get this code to work in any version of Excel. The formula you are assigning to mydata1 has a syntax error. I will bet that you changed the string for mydata1. It has an unmatched single quote. You don't need the single quote here anyway. Try this:

mydata1 = "=c:\extract data\[r.xls]Sheet1!$A$1:$A$10"

or if you want to use the quotes

mydata1 = "=c:\extract data\'[r.xls]Sheet1'!$A$1:$A$10"

**kevincoxshall** · 04-03-2014, 03:52 AM

Ok ta the mydata1 = line works fine now but the "with end with" is not working

Any suggestions.....

Kevin

**JosephP** · 04-03-2014, 04:02 AM

what do you mean the with end with isn't working? (it's actually pointless here anyway because it's more typing!)

**kevincoxshall** · 04-03-2014, 04:04 AM

All I want to do is extract 10 numbers from a closed workbook where I can define the path and name of the workbook.
Then put them in the current workbook
Kev

**JosephP** · 04-03-2014, 04:12 AM

Sub DataFromClosedBook()
Dim stPath as string, stFile as string
stPath = "c:\extract data\"
stFile = "r.xls"
ThisWorkbook.Worksheets(1).Range("a1:a10").Formula = "='" & stPath & "[" & stFile & "]Sheet1'!$A$1:$A$10"
End Sub

**kevincoxshall** · 04-03-2014, 10:46 AM

JosephP this is superb it works thanks

Kevin

Old code not working in 2010

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View