MartinS
Active Member
- Joined
- Jun 17, 2003
- Messages
- 490
- Office Version
- 365
- Platform
- Windows
I'm having some difficulty with an existing process which opens an existing CSV (or sometimes xlsx) file, manipulates the data within it in memory (loads range into a Variant array and reads through the data in it and creates a summarised (shorter) array), then clears the opened sheet and writes the new data back (from the summary array) before saving the opened file with a fixed file name/format.
Originally we opened the file using Workbooks.Open, because the data had been consistent, i.e. the first column contained an alpha numeric string (for anything from a few hundred to a few hundred thousand rows), the next column was, again, an alpha numeric indicator and then a couple of numeric columns. We were then sent a file which started with around 1,000 numeric only strings (zero prefixed) before switching to the default alphanumeric strings in the first column, so when it was opened using .Open, the numeric values were displayed and stored as numbers, i.e. "01234567" loaded as 1234567 (even though, in the CSV file, the value was text delimited with double quotes).
Before we manipulated the data loaded into the array, it had to be sorted by the first column, but with the numeric values present, sorting would throw out the expected order, i.e. '9998078’ is seen as greater than the text value ‘10402045’ (as we are comparing strings in the sort using StrComp), but the data HAS to be sorted correctly before we can continue.
So we then decided to make a change and, in this specific stage, switched to opening the file as Text using OpenText (only during this specific part of the code IF the file was already a CSV). We expected this change to allow us to control the formatting of the columns , but it didn't work. So after some googling, it came to light that we needed to change the file type from CSV to a flat text type, ie DIF or TXT. When opening the newly renamed file, it then opened correctly taking the FileInfo property of the OpenText call and correctly formatting the first two columns as text.
The rest of the changes were made to rename the file back when the process has completed, before we realised that the saved file (the summary data written back to the open TXT file) wasn't comma delimited. We assume because we saved a TXT file to a CSV, rather than a CSV or Excel to CSV?
So, my question - is it possible, when saving an open flat text file, to force Excel to save as comma delimited? Or would I be better to close the opened file completely once it's content is in memory and then open a new file when I'm ready? I have probably answered my own question, but wanted to be sure I wasn't missing something that would allow me not to have to rewrite existing code that, until now, has worked.
Any suggestions gratefully received.
Thanks
Martin
Originally we opened the file using Workbooks.Open, because the data had been consistent, i.e. the first column contained an alpha numeric string (for anything from a few hundred to a few hundred thousand rows), the next column was, again, an alpha numeric indicator and then a couple of numeric columns. We were then sent a file which started with around 1,000 numeric only strings (zero prefixed) before switching to the default alphanumeric strings in the first column, so when it was opened using .Open, the numeric values were displayed and stored as numbers, i.e. "01234567" loaded as 1234567 (even though, in the CSV file, the value was text delimited with double quotes).
Before we manipulated the data loaded into the array, it had to be sorted by the first column, but with the numeric values present, sorting would throw out the expected order, i.e. '9998078’ is seen as greater than the text value ‘10402045’ (as we are comparing strings in the sort using StrComp), but the data HAS to be sorted correctly before we can continue.
So we then decided to make a change and, in this specific stage, switched to opening the file as Text using OpenText (only during this specific part of the code IF the file was already a CSV). We expected this change to allow us to control the formatting of the columns , but it didn't work. So after some googling, it came to light that we needed to change the file type from CSV to a flat text type, ie DIF or TXT. When opening the newly renamed file, it then opened correctly taking the FileInfo property of the OpenText call and correctly formatting the first two columns as text.
Code:
Workbooks.OpenText Filename:=txtFilename, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Comma:=True, Local:=True, _
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 1), Array(5, 1))So, my question - is it possible, when saving an open flat text file, to force Excel to save as comma delimited? Or would I be better to close the opened file completely once it's content is in memory and then open a new file when I'm ready? I have probably answered my own question, but wanted to be sure I wasn't missing something that would allow me not to have to rewrite existing code that, until now, has worked.
Any suggestions gratefully received.
Thanks
Martin
