Ralph Howarth wrote...
I have a download report in *.csv that I want to use MS Excel to
convert into
another *.csv every week. The purpose is to prepare transactions for
importing into accounting software as new transactions.
The report download name is made up of a text string that is related
to a
User ID such that the report is received as "ReportName<UserID.csv"
The
worksheet in the report is the same name as the file name. The report
has
the data header on row 3 and the transaction records begin at row 4 to
a
variable number of rows.
1) How can that data be read and replaced in a worksheet of a
reusable
workbook such that old data of a previous copy paste is removed and
fresh
data laid down so formulas can read the new data?
2) How can all references to the report file be mass modified when
the
UserID changes?
You're transforming one text file to another text file. Spreadsheets
are not the best tool for doing this. Even if the transformation is
fairly complicated, you could write a filter program in VBScript (which
is almost certainly installed on your machine) to transform the CSV
file. Without details of what exactly you're doing to the text file,
that's all I'll say about that.
For #1, if you never save the .XLS file that has the formulas that
convert the original CSV file into the transformed CSV file, but only
save the transformed CSV file, then you could just close and reopen
this .XLS file for each report. Otherwise, all it takes is clicking in
the box in the top-left of the worksheet frame (to the left of the
column letters and above the row numbers) and press [Delete]. Are you
looking for a macro?
For #2, if you're copying the original CSV files into an .XLS file, and
if previously transformed CSV files were cleared from the .XLS file's
'import' worksheet, why would you have any references to the CSV file
to change?
You need to provide more details about how you want this to work.
For #1 and #2, are you opening the CSV file as a separate workbook then
copying it into a worksheet in the workbook that creates the new CSV
file? Are you then generating the new CSV file in a different worksheet
of that workbook?
|