gmulhall
04-29-2007, 08:48 PM
Hi,
When I load data to a sheet using the following code
Sheet1.Select
Sheet1.Columns("A:Z").Delete
With Sheet1.QueryTables.Add("TEXT;" & "C:\Data.csv", Range("A1"))
.Name = "DoesNotMatter"
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileCommaDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1)
.Refresh BackgroundQuery:=False
End With
I find that all my formulas in say Sheet2 which use vlookup to extract data no longer work and show a REF! error.
ie
=VLOOKUP($E$2,'Weekly Data'!$A2:$J$500,7,FALSE)
becomes
=VLOOKUP($E$2,'Weekly Data'!REF!,7,FALSE)
That causes me to have to re-enter the formulas which is painful.
It does not happen if I paste in the data using copy paste however.
I'm using Office 2007.
Is there a way to prevent this ?
Any help appreciated.
Thanks,
Geoff :think:
When I load data to a sheet using the following code
Sheet1.Select
Sheet1.Columns("A:Z").Delete
With Sheet1.QueryTables.Add("TEXT;" & "C:\Data.csv", Range("A1"))
.Name = "DoesNotMatter"
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileCommaDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1)
.Refresh BackgroundQuery:=False
End With
I find that all my formulas in say Sheet2 which use vlookup to extract data no longer work and show a REF! error.
ie
=VLOOKUP($E$2,'Weekly Data'!$A2:$J$500,7,FALSE)
becomes
=VLOOKUP($E$2,'Weekly Data'!REF!,7,FALSE)
That causes me to have to re-enter the formulas which is painful.
It does not happen if I paste in the data using copy paste however.
I'm using Office 2007.
Is there a way to prevent this ?
Any help appreciated.
Thanks,
Geoff :think: