Excel2000: Opening *.csv through VBA and International Settings
 

Hi

A Firm leasing various IT devices has a web page, where customers can see
various info about their leased devices. There is a possibility to save this
info as a CSV-file.

When I open such CSV-file directly from Excel, then it's all OK - all data
is in right columns (there is 22 of them).
When I open the same CSV-file from VBA (I need to generate rental payments
table from it), then all data are messed up hopeless. The code which opens
the CSV-file is:
....
' Asking for source file
varFileTitle = "Open source file!"
varFileFilter = "Templates (*.CSV), *.CSV"
varSourceFile = Application.GetOpenFilename(Title:=varFileTitle,
FileFilter:=varFileFilter)
If varSourceFile = "False" Then
MsgBox ("Data import was stopped because no source file was
selected!")
Exit Sub
End If

' Opening source workbook
Workbooks.Open Filename:=varSourceFile
....

The reason is, that the CSV-file is using Estonian settings - semicolon is
used as data delimiter. But when CSV-file is opened from VBA, comma is used
as data delimiter instead. (In Estonian settings, comma is used as decimal
delimiter, and additionally in CSV-table in some columns with text data
[and in some column headers] the comma is simply used for punctuation).

How to force Excel VBA to use semicolons as delimiters when opening a
CSV-file. When there doesn't exist a way to do this, I have to open the
CSV-file as an ASCII file, and to use some heavy programming to read all
data into Excel - I hope I can avoid this.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )

Excel2000: Opening *.csv through VBA and International Settings
 

Hi Arvi,

Couldn't you rename the file a .txt file? If you then manually open it you get a wizard in which you can specify date formats ans
separators. You might be able to record that in a macro and use that as a start for your own code.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Arvi Laanemets" wrote in message ...
| Hi
|
| A Firm leasing various IT devices has a web page, where customers can see
| various info about their leased devices. There is a possibility to save this
| info as a CSV-file.
|
| When I open such CSV-file directly from Excel, then it's all OK - all data
| is in right columns (there is 22 of them).
| When I open the same CSV-file from VBA (I need to generate rental payments
| table from it), then all data are messed up hopeless. The code which opens
| the CSV-file is:
| ...
| ' Asking for source file
| varFileTitle = "Open source file!"
| varFileFilter = "Templates (*.CSV), *.CSV"
| varSourceFile = Application.GetOpenFilename(Title:=varFileTitle,
| FileFilter:=varFileFilter)
| If varSourceFile = "False" Then
| MsgBox ("Data import was stopped because no source file was
| selected!")
| Exit Sub
| End If
|
| ' Opening source workbook
| Workbooks.Open Filename:=varSourceFile
| ...
|
| The reason is, that the CSV-file is using Estonian settings - semicolon is
| used as data delimiter. But when CSV-file is opened from VBA, comma is used
| as data delimiter instead. (In Estonian settings, comma is used as decimal
| delimiter, and additionally in CSV-table in some columns with text data
| [and in some column headers] the comma is simply used for punctuation).
|
| How to force Excel VBA to use semicolons as delimiters when opening a
| CSV-file. When there doesn't exist a way to do this, I have to open the
| CSV-file as an ASCII file, and to use some heavy programming to read all
| data into Excel - I hope I can avoid this.
|
|
| --
| Arvi Laanemets
| ( My real mail address: arvi.laanemets<attarkon.ee )
|
|

Excel2000: Opening *.csv through VBA and International Settings
 

Hi


Couldn't you rename the file a .txt file? If you then manually open it you
get a wizard in which you can specify date formats ans
separators. You might be able to record that in a macro and use that as a
start for your own code.



I'm afraid I can't :-(((
I'm trying to avoid any manual editing when possible - it'll be extremly
difficult to explain to ladies in financial department how they must do it,
and to be sure they never don't do anything in wrong way :-))
As end result, leasing payments data (DeviceNo, Date, Sum) must be imported
into Access database. I can't read CSV-file directly into Access, because it
isn't fully in table format. And anyway I have to generate wholly new table
based on CSV table, because CSV table contains a single row for every
device - along with payment sums for payment period, payment period lengths,
payment period start and end dates, etc. And the CSV-file name is generated
auttomatically and is always unique - no way there to link it into Access
without manual work again.

With an Excel file as intermediate step I hoped to minimize possible
missteps. The needed steps would be:
1. The user opens web page, clicks on "Export to Excel", and saves the
CSV-file.
2. Then he/she opens special Excel file, clicks on button "Import Data", and
points on saved CSV-file - the new lease payments table is generated. (This
table is always linked to Access database). The Excel file is closed.
3. When the Access form with lease info is activated, and there exist any
rows (determined by DeviceNo and PaymentDate) in linked Excel file, which
are missing in according Access table, or have different Sum, a button
"Import lease payments for <FirmName" is displayed. When the button is
clicked, all missing data are imported, and all changed data are edited. The
button is hidden again after that.


Thanks for a try anyway.
(With an additional saving step, there is no need for text file - a better
way is to save the manually opened CSV-file in Excel format)



--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )

Excel2000: Opening *.csv through VBA and International Settings
 

Hi Arvi,

I suppose you use XL2003, because I had similar tasks and when working with
XL2000 (Hungarian settings are the same as Estonian) it recognized correctly
the semicolon separated text files, but after having upgraded to XL2003 I
experienced the same as you.

I found a workaround:
Opened the .CSV file:
Workbooks.Open Filename:=feladfuzet
'count No of rows
feladdarab = Columns("A:A").Find("*", [A1], , , xlByRows,
xlPrevious).Row
Call CSVtoXL(feladdarab)

I recorded a macro using Text to Columns and made a sub from it:
Sub CSVtoXL(sorokszama)
Dim Terjedelem As Range
Set Terjedelem = Range("A1:A" & sorokszama)
Terjedelem.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5,
1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1),
Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18,
1), Array(19, 1), Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1),
Array(25, 1), Array(26, 1), _
Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31,
1), Array(32, 1), Array( _
33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1),
Array(38, 1), Array(39, 1), _
Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44,
1), Array(45, 1), Array( _
46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1),
Array(51, 1), Array(52, 1), _
Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57,
1), Array(58, 1), Array( _
59, 1), Array(60, 1), Array(61, 1), Array(62, 1), Array(63, 1),
Array(64, 1), Array(65, 1), _
Array(66, 1), Array(67, 1), Array(68, 1), Array(69, 1), Array(70,
1), Array(71, 1), Array( _
72, 1), Array(73, 1), Array(74, 1)), TrailingMinusNumbers:=True
End Sub


Not very nice but works! What can we do if Microsoft decides to change
working solutions to non-working ones? I hope it helps to ou!

Regards,
Stefi

€žArvi Laanemets€� ezt Ã*rta:

Hi

A Firm leasing various IT devices has a web page, where customers can see
various info about their leased devices. There is a possibility to save this
info as a CSV-file.

When I open such CSV-file directly from Excel, then it's all OK - all data
is in right columns (there is 22 of them).
When I open the same CSV-file from VBA (I need to generate rental payments
table from it), then all data are messed up hopeless. The code which opens
the CSV-file is:
....
' Asking for source file
varFileTitle = "Open source file!"
varFileFilter = "Templates (*.CSV), *.CSV"
varSourceFile = Application.GetOpenFilename(Title:=varFileTitle,
FileFilter:=varFileFilter)
If varSourceFile = "False" Then
MsgBox ("Data import was stopped because no source file was
selected!")
Exit Sub
End If

' Opening source workbook
Workbooks.Open Filename:=varSourceFile
....

The reason is, that the CSV-file is using Estonian settings - semicolon is
used as data delimiter. But when CSV-file is opened from VBA, comma is used
as data delimiter instead. (In Estonian settings, comma is used as decimal
delimiter, and additionally in CSV-table in some columns with text data
[and in some column headers] the comma is simply used for punctuation).

How to force Excel VBA to use semicolons as delimiters when opening a
CSV-file. When there doesn't exist a way to do this, I have to open the
CSV-file as an ASCII file, and to use some heavy programming to read all
data into Excel - I hope I can avoid this.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )

Excel2000: Opening *.csv through VBA and International Settings
 

Hi


"Stefi" wrote in message
...



I suppose you use XL2003, because I had similar tasks and when working
with


No, it's Excel2000 (but we have Outlook2003 installed in all our computers,
because Exchange Server doesn't co-operate well with Outlook2000).
Btw., Access2000 in same computer recognizes semicolons as delimiters, but I
as I explained in another posting, in Access there will be other problems.


XL2000 (Hungarian settings are the same as Estonian) it recognized
correctly
the semicolon separated text files, but after having upgraded to XL2003 I
experienced the same as you.

I found a workaround:
Opened the .CSV file:
Workbooks.Open Filename:=feladfuzet
'count No of rows
feladdarab = Columns("A:A").Find("*", [A1], , , xlByRows,
xlPrevious).Row
Call CSVtoXL(feladdarab)

I recorded a macro using Text to Columns and made a sub from it:
Sub CSVtoXL(sorokszama)
Dim Terjedelem As Range
Set Terjedelem = Range("A1:A" & sorokszama)
Terjedelem.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1),
Array(5,
1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1),
Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1),
Array(18,
1), Array(19, 1), Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1),
Array(25, 1), Array(26, 1), _
Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31,
1), Array(32, 1), Array( _
33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1),
Array(38, 1), Array(39, 1), _
Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44,
1), Array(45, 1), Array( _
46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1),
Array(51, 1), Array(52, 1), _
Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57,
1), Array(58, 1), Array( _
59, 1), Array(60, 1), Array(61, 1), Array(62, 1), Array(63, 1),
Array(64, 1), Array(65, 1), _
Array(66, 1), Array(67, 1), Array(68, 1), Array(69, 1), Array(70,
1), Array(71, 1), Array( _
72, 1), Array(73, 1), Array(74, 1)), TrailingMinusNumbers:=True
End Sub


Not very nice but works! What can we do if Microsoft decides to change
working solutions to non-working ones? I hope it helps to ou!



With first opening, the data is split into columns (different number of them
row-wise), because there are p.e. numbers like 1234,50. Your workaround
works only, when all data remain in column A. Of-course I can concantenate
them before spliting again, but there will be some spaces lost in text
entries for sure. And this all will be even more cumbersome.


Thanks anyway

--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )

Excel2000: Opening *.csv through VBA and International Settings
 

Can't you import the file as one column (maybe by renaming it .txt first) and then use the DataText to columns feature, where you
can specify a semicolon as separator?
This could all be hidden in a macro so the users doesn't see it.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Arvi Laanemets" wrote in message ...
| Hi
|
|
| Couldn't you rename the file a .txt file? If you then manually open it you
| get a wizard in which you can specify date formats ans
| separators. You might be able to record that in a macro and use that as a
| start for your own code.
|
|
|
| I'm afraid I can't :-(((
| I'm trying to avoid any manual editing when possible - it'll be extremly
| difficult to explain to ladies in financial department how they must do it,
| and to be sure they never don't do anything in wrong way :-))
| As end result, leasing payments data (DeviceNo, Date, Sum) must be imported
| into Access database. I can't read CSV-file directly into Access, because it
| isn't fully in table format. And anyway I have to generate wholly new table
| based on CSV table, because CSV table contains a single row for every
| device - along with payment sums for payment period, payment period lengths,
| payment period start and end dates, etc. And the CSV-file name is generated
| auttomatically and is always unique - no way there to link it into Access
| without manual work again.
|
| With an Excel file as intermediate step I hoped to minimize possible
| missteps. The needed steps would be:
| 1. The user opens web page, clicks on "Export to Excel", and saves the
| CSV-file.
| 2. Then he/she opens special Excel file, clicks on button "Import Data", and
| points on saved CSV-file - the new lease payments table is generated. (This
| table is always linked to Access database). The Excel file is closed.
| 3. When the Access form with lease info is activated, and there exist any
| rows (determined by DeviceNo and PaymentDate) in linked Excel file, which
| are missing in according Access table, or have different Sum, a button
| "Import lease payments for <FirmName" is displayed. When the button is
| clicked, all missing data are imported, and all changed data are edited. The
| button is hidden again after that.
|
|
| Thanks for a try anyway.
| (With an additional saving step, there is no need for text file - a better
| way is to save the manually opened CSV-file in Excel format)
|
|
|
| --
| Arvi Laanemets
| ( My real mail address: arvi.laanemets<attarkon.ee )
|
|

Excel2000: Opening *.csv through VBA and International Setting
 

Yes, I had a luck because my CSV file was imported from a DOS system in which
decimal delimiter was represented by "." (period) therefore rows were not
broken, all data remained in column A!

Regards,
Stefi

Excel2000: Opening *.csv through VBA and International Settings
 

OK. I decided for saving CSV-file manually to *.xls. So currently steps to
get payment table into Access a
1. User saves the data from web page as CSV-file.
2. User opens CSV-file with Excel, and saves as Excel workbook - all columns
are split properly.
3. User opens special Excel file, and starts a procedure, which generates a
new lease payments table. The Excel file is saved and closed automatically.
4. In Access project, when any changed data appear in linked Excel table,
user can start a procedure for refreshing lease payments table.

An additional step compared to original plan (no. 2), but an easy one. And
as bonus, user can replace CSV-file name, which is a random sequence of
numbers, with a more meaningfull one.


Arvi Laanemets

Excel2000: Opening *.csv through VBA and International Settings
 

What about making a web query from Excel directly ?

NickHK

"Arvi Laanemets" wrote in message
...
OK. I decided for saving CSV-file manually to *.xls. So currently steps to
get payment table into Access a
1. User saves the data from web page as CSV-file.
2. User opens CSV-file with Excel, and saves as Excel workbook - all
columns
are split properly.
3. User opens special Excel file, and starts a procedure, which generates
a
new lease payments table. The Excel file is saved and closed
automatically.
4. In Access project, when any changed data appear in linked Excel table,
user can start a procedure for refreshing lease payments table.

An additional step compared to original plan (no. 2), but an easy one. And
as bonus, user can replace CSV-file name, which is a random sequence of
numbers, with a more meaningfull one.


Arvi Laanemets

Excel2000: Opening *.csv through VBA and International Settings
 

Hi


"NickHK" wrote in message
...
What about making a web query from Excel directly ?


At first, this web query thing is a thin ice form me. I tried it once
following Excel Help, but the wizard wanted some table name I didn't have
any clue at. And Help was as useless as always :-)) . As there wasn't any
urgent need for it, then I dropped it.

And as second, the original table has little use for me, and I can't
generate a new one from it using query language only. In Excel, I have some
earlier-made procedures which I modified slightly, and which now will do all
this work in a go. Maybe in future I'll write some equivalent for Access
too, but at moment I didn't have any time for it.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )

Excel2000: Opening *.csv through VBA and International Settings
 

What is the web page address ?

NickHK

"Arvi Laanemets" wrote in message
...
Hi


"NickHK" wrote in message
...
What about making a web query from Excel directly ?


At first, this web query thing is a thin ice form me. I tried it once
following Excel Help, but the wizard wanted some table name I didn't have
any clue at. And Help was as useless as always :-)) . As there wasn't any
urgent need for it, then I dropped it.

And as second, the original table has little use for me, and I can't
generate a new one from it using query language only. In Excel, I have
some
earlier-made procedures which I modified slightly, and which now will do
all
this work in a go. Maybe in future I'll write some equivalent for Access
too, but at moment I didn't have any time for it.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )

Excel2000: Opening *.csv through VBA and International Settings
 

It is accessible only through log-in for registred clients.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )