Excel: formula changes after importing data from csv file

[Calator]

Data is imported in Sheet1 from a csv file, using following VBA code:
Worksheets(strTargetSheet).Activate
Range("A1").Select

With Selection.QueryTable
.Connection = _
"TEXT;" & CSVFileLocation & "\" & strImportFile
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileCommaDelimiter = True
.TextFilePromptOnRefresh = False
.Refresh BackgroundQuery:=False
End With

Sheet2 is a second sheet, that agregates data from Sheet1 and has formulas like:
=Sheet1.R5+Sheet1.R7

After the CSV Import routine is executed, the formulas in Sheet2 have all changed and point to column "T" instead of column "R". Eg
=Sheet1.T5+Sheet1.T7

However my data has been imported ok in Sheet1 in column R.
Therefore all the cells in Sheet2 are blank as they point to column T where there is no data.

Note that formulas for columns A-Q are left ok - did not change. Only formulas in column R have been messed up.

Any ideas??

 

[combo]

Looks like you inserted two columns in sheet1 between cols A and R in the meantime.

combo

 

[combo]

When importing data excel inserts new columns, so INDIRECT function can be useful to break direct link to source range.

combo

 

[SkipVought]

Greetings mate,

I'd also advise against using Activate and Select...

With Worksheets(strTargetSheet).Range("A1").QueryTable
    .Connection = _
    "TEXT;" & CSVFileLocation & "\" & strImportFile
    .TextFilePlatform = xlWindows
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileCommaDelimiter = True
    .TextFilePromptOnRefresh = False
    .Refresh BackgroundQuery:=False
End With

Could also be...

With Worksheets(strTargetSheet).QueryTables(1)

assuming that it is the only QT on the sheet.

Also, unless you are CHANGING the Connection String, or any of the .TextFile parameters, the only code you really need to refresh the data is...

Worksheets(strTargetSheet).[A1].QueryTable.Refresh BackgroundQuery:=False

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[Calator]

hi Guys,

Combo: function "Indirect" does note exist in VBA HELP
SkipVought: I tried your re-coded QueryTable but problem was still the same. Note that the sub-routine is called repeteadly to import data from several files into several sheets, not just the one, and the problem manifests in every sheet

I could see a 'named range' in Sheet 1 which extends only to column Q, it does not include my added column R. After I extended that range to include R, the problem was solved.
I could not see where and how that range is referred to, in the VBA code, and how the range was changed after importing the data, to extend to column S. 'Strange'!
If you had any explanation pls let me know. It obviously is not a good design, if we add columns in the extract file, it's causing problems.

 

[combo]

It is a worksheet function, I assumed that Sheet1 is a report page, linked with data sheet (Sheet2). If you need to fill it with VBA, your problem does not exist.

combo

 

[xlbo]

nope nope and nope.

Your issue is caused by settings within your query table

Right click on the data returned by your query and choose "Data Range Properties"

you probably have "Insert cells for new data, delete unused cells" currently ticked. This produces the same results as manually inserting and deleting cells - changing formula references...

To change this behaviour, change the option at the bottom to "Overwrite existing cells with new data, clear unused cells"

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

"I could see a 'named range' in Sheet 1 which extends only to column Q, it does not include my added column R. After I extended that range to include R, the problem was solved. "


The QueryTabe has a system-generated NamedRange. You cannot change that range. You can do this, however...

With Worksheets(strTargetSheet).[A1]
   .QueryTable.Refresh BackgroundQuery:=False[b]
   Set rngExtended = .CurrentRegion[/b]
End With

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[SkipVought]

"...the sub-routine is called repeteadly to import data from several files into several sheets..."

dim ws as worksheet, qt as querytable
for each ws in worksheets
   for each qt in ws.querytables
      with qt
         .Refresh BackgroundQuery:=False
         set rngExtended = .resultrange.currentregion
      end with
   next
next

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}