How to fill a sheet in Excel2003 with CSV data?

  • Thread starter Thread starter tmarko
  • Start date Start date
T

tmarko

I use server-side calculations in an OWC(Office spreadsheet component)
send the resulting data as string (formatted as CSV) to my Client. Ho
can I fast fill an Excelsheet(2003) with this data passed as string
Must I loop through the CSV data? When saved as file (.csv) on th
server I can open the .CSV manually on the client fast like opening a
ordinary .xla file. But how do I do it programmatically when the cs
data is in a string on my VBA client? Cannot find the proper propertie

tmark
 
I don't know much about the Office spreadsheet component, but it might be
easier to pick up you data as an array in a variant and write it out the
same way

Pseudocode follows:

Dim vArr as Variant

vArr = OWC.Range("A1:Z200")

then on the client side

ActiveSheet.Range("A1").Resize(ubound(varr,1),ubound(varr,2)).Value = _
vArr
 
Hi Tom Ogilvy!

I cannot get your code working. The spreadsheet info is not importan
after all just info why I pass CSV data to the Client.

I want to populate an Excelsheet with CSV data from this string as fas
as possible (Excel2003). I was hoping for a property that is found i
Worksheet or something to fill the sheet. Instead of looping throug
the file beacuse when open up a .SCV file everything seems to go b
magic. But I do not want to involve a file in my application.

Thanks anywa

tmark
 
The closest you could come is to get the string in the clipboard and then
paste it to the sheet into cell A1 (as an example). This should put all the
data in column A, then you could use the Text to Columns Method under the
Data menu to parse your data into the appropriate columns.

put some data in A1 and A2 like below

123,"abc",345,"cdef","ghij",456
"iii",31111,"rst",1,"mnop",,345

Then turn on the macro recorder

select A1:A2

then do
Data=>Text to Columns
Choose delimited and Next
Choose Comma as the separator (if that is your separator)
Then click finish
Turn off the macro recorder

This produces:

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, 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))

So once you put your string in column 1, you can run this and it should do
the equivalent of opening a csv file.

(change selection to something like
Activesheet.Range("A1").CurrentRegion.TextToColumns . . .
 
Back
Top