I have sheet that I've set up that imports data from .txt files created by
another program. I have the query tailored so that it deletes the first 4
rows of the .txt file, as those rows are extraneous info, not the data.

When I run the query on a different file than the one that I created it for,
it is deleting a row from the sheet, which throws the entire sheet off and
feeds column after column of "!REF#" into the following calculation columns,
since this row shift blows everything out of the water. These text files are
all in the same format, and vary only in that some of them have three rows of
data, while others can have up to six. I "blue sky'd" my import space & have
23 rows available for the import.

Any ideas of what's happening here, or any questions that might get me
pointed in the right direction for an answer?

TIA

Do you have any merged cells in the sheet you are importing to? There can be
unpredictable results when importing to merged cells I have found.

"Cyberbiker" wrote:

I have sheet that I've set up that imports data from .txt files created by
another program. I have the query tailored so that it deletes the first 4
rows of the .txt file, as those rows are extraneous info, not the data.

When I run the query on a different file than the one that I created it for,
it is deleting a row from the sheet, which throws the entire sheet off and
feeds column after column of "!REF#" into the following calculation columns,
since this row shift blows everything out of the water. These text files are
all in the same format, and vary only in that some of them have three rows of
data, while others can have up to six. I "blue sky'd" my import space & have
23 rows available for the import.

Any ideas of what's happening here, or any questions that might get me
pointed in the right direction for an answer?

TIA

Thanks for the reply, tim.

I wasn't importing to any merged cells, but I found the problem. In the
"External Data Range Properties" sheet, the radio button for "Insert cells
for new data, delete unused cells" was selected. Since my data range can
fluctuate, I needed to click the "overwrite existing cells with new data,
clear unused cells" radio button, along with checking the "Fill down formulas
in columns adjacent to data" box.

I think that when it was getting a shorter data set, it was deleting entire
rows, which threw off my formulas, since referenced figures didn't exist
anymore. It seems to work now, which is the big thing.

Thanks again for your reply.



"tim m" wrote:

Do you have any merged cells in the sheet you are importing to? There can be
unpredictable results when importing to merged cells I have found.

"Cyberbiker" wrote:

I have sheet that I've set up that imports data from .txt files created by
another program. I have the query tailored so that it deletes the first 4
rows of the .txt file, as those rows are extraneous info, not the data.

When I run the query on a different file than the one that I created it for,
it is deleting a row from the sheet, which throws the entire sheet off and
feeds column after column of "!REF#" into the following calculation columns,
since this row shift blows everything out of the water. These text files are
all in the same format, and vary only in that some of them have three rows of
data, while others can have up to six. I "blue sky'd" my import space & have
23 rows available for the import.

Any ideas of what's happening here, or any questions that might get me
pointed in the right direction for an answer?

TIA