Excel automation

[cj]

I want to start a workbook and add sheets to it one at a time.

Right now my code opens a workbook and it has 3 sheets alread in it and
I don't even know how to move from one to another. Any help would be
appreciated. Here is my code.

Dim oXL As Excel.Applicati on
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet

oXL = CreateObject("E xcel.Applicatio n")
oXL.Visible = True

oWB = oXL.Workbooks.A dd

[rowe_newsgroups]

Re: Excel automation

I would check out the microsoft.publi c.excel.program ming newsgroup.
It's almost all vb6 and excel vba, but you can easily convert the
samples to .NET syntax. As far as selecting a sheet in excel, i believe
it goes something like this: (be warned I don't have excel on this PC,
I'll check back tomorrow at work to see if I typed something wrong)

oXL.ActiveWorkb ook.Sheets("She et1").Select

If the excel programming news group doesn't deal with a specific .NET
question please post back and I'll try to help you out.

Thanks,

Seth Rowe


cj wrote:

I want to start a workbook and add sheets to it one at a time.
>
Right now my code opens a workbook and it has 3 sheets alread in it and
I don't even know how to move from one to another. Any help would be
appreciated. Here is my code.
>
Dim oXL As Excel.Applicati on
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
>
oXL = CreateObject("E xcel.Applicatio n")
oXL.Visible = True
>
oWB = oXL.Workbooks.A dd

[Peter Huang [MSFT]]

RE: Excel automation

Hi

We can use the Automation code as below to delete the another two sheets.
Imports Excel = Microsoft.Offic e.Interop.Excel
Module Module1
Sub Main()
Dim oXL As Excel.Applicati on
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
oXL = CreateObject("E xcel.Applicatio n")
oXL.Visible = True
oWB = oXL.Workbooks.A dd
Dim i As Integer = 0
For Each oSheet In oWB.Sheets
i += 1
If i = 1 Then
Continue For
Else
oSheet.Delete()
End If
Next
End Sub
End Module

You may have a try and let me know the result.
BTW: For automation Office or Excel related programming, you may also try
the newsgroup below.
microsoft.publi c.office.develo per.automation
microsoft.publi c.excel.program ming

Best regards,

Peter Huang

Microsoft Online Community Support
=============== =============== =============== =====
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=============== =============== =============== =====
This posting is provided "AS IS" with no warranties, and confers no rights.

[cj]

Re: Excel automation

Thanks

Peter Huang [MSFT] wrote:

Hi
>
We can use the Automation code as below to delete the another two sheets.
Imports Excel = Microsoft.Offic e.Interop.Excel
Module Module1
Sub Main()
Dim oXL As Excel.Applicati on
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
oXL = CreateObject("E xcel.Applicatio n")
oXL.Visible = True
oWB = oXL.Workbooks.A dd
Dim i As Integer = 0
For Each oSheet In oWB.Sheets
i += 1
If i = 1 Then
Continue For
Else
oSheet.Delete()
End If
Next
End Sub
End Module
>
You may have a try and let me know the result.
BTW: For automation Office or Excel related programming, you may also try
the newsgroup below.
microsoft.publi c.office.develo per.automation
microsoft.publi c.excel.program ming
>
Best regards,
>
Peter Huang
>
Microsoft Online Community Support
=============== =============== =============== =====
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=============== =============== =============== =====
This posting is provided "AS IS" with no warranties, and confers no rights.
>

[cj]

Re: Excel automation

Thanks

rowe_newsgroups wrote:

I would check out the microsoft.publi c.excel.program ming newsgroup.
It's almost all vb6 and excel vba, but you can easily convert the
samples to .NET syntax. As far as selecting a sheet in excel, i believe
it goes something like this: (be warned I don't have excel on this PC,
I'll check back tomorrow at work to see if I typed something wrong)
>
oXL.ActiveWorkb ook.Sheets("She et1").Select
>
If the excel programming news group doesn't deal with a specific .NET
question please post back and I'll try to help you out.
>
Thanks,
>
Seth Rowe
>
>
cj wrote:

>I want to start a workbook and add sheets to it one at a time.
>>
>Right now my code opens a workbook and it has 3 sheets alread in it and
>I don't even know how to move from one to another. Any help would be
>appreciated. Here is my code.
>>
>Dim oXL As Excel.Applicati on
>Dim oWB As Excel.Workbook
>Dim oSheet As Excel.Worksheet
>>
>oXL = CreateObject("E xcel.Applicatio n")
>oXL.Visible = True
>>
>oWB = oXL.Workbooks.A dd

>

[Peter Huang [MSFT]]

Re: Excel automation

Hi.

You are welcomed.

Anyway, if you have any concern on this issue, please feel free to let me
know and I am happy to be of assistance.


Best regards,

Peter Huang

Microsoft Online Community Support
=============== =============== =============== =====
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=============== =============== =============== =====
This posting is provided "AS IS" with no warranties, and confers no rights.

[cj]

Re: Excel automation

Peter, Here's a question for you. I have approximately 170,000 records
to import into excel (they are in a file formatted as a printable ascii
report now). To get them into excel I see two options. What I'm doing
now is to read each line of the report and for data lines add them to
the excel sheet. When I've added 65000 rows I start adding to a new
sheet. This takes forever. Would it be faster to turn the report into
multiple 65000 record comma delimited files and then import them into
excel? Is it possible to import them automatically?

Peter Huang [MSFT] wrote:

Hi.
>
You are welcomed.
>
Anyway, if you have any concern on this issue, please feel free to let me
know and I am happy to be of assistance.
>
>
Best regards,
>
Peter Huang
>
Microsoft Online Community Support
=============== =============== =============== =====
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=============== =============== =============== =====
This posting is provided "AS IS" with no warranties, and confers no rights.
>

[rowe_newsgroups]

Re: Excel automation

If you can hit the data with SQL statements you could use excel's
querytables to do a much faster import.

Thanks,

Seth Rowe


cj wrote:

Peter, Here's a question for you. I have approximately 170,000 records
to import into excel (they are in a file formatted as a printable ascii
report now). To get them into excel I see two options. What I'm doing
now is to read each line of the report and for data lines add them to
the excel sheet. When I've added 65000 rows I start adding to a new
sheet. This takes forever. Would it be faster to turn the report into
multiple 65000 record comma delimited files and then import them into
excel? Is it possible to import them automatically?
>
Peter Huang [MSFT] wrote:

Hi.

You are welcomed.

Anyway, if you have any concern on this issue, please feel free to let me
know and I am happy to be of assistance.


Best regards,

Peter Huang

Microsoft Online Community Support
=============== =============== =============== =====
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=============== =============== =============== =====
This posting is provided "AS IS" with no warranties, and confers no rights.

[cj]

Re: Excel automation

Yes, writing a CSV file and importing by hand is instantaneous in
comparison. to loading each cell from VB. Now I'd love to know how to
have VB open Excel and import from 1 to 4 files as sheets in a workbook.
I'll look into it some more tomorrow but if you can give me any
pointers I'd appreciate it.

cj wrote:

Peter, Here's a question for you. I have approximately 170,000 records
to import into excel (they are in a file formatted as a printable ascii
report now). To get them into excel I see two options. What I'm doing
now is to read each line of the report and for data lines add them to
the excel sheet. When I've added 65000 rows I start adding to a new
sheet. This takes forever. Would it be faster to turn the report into
multiple 65000 record comma delimited files and then import them into
excel? Is it possible to import them automatically?
>
Peter Huang [MSFT] wrote:

>Hi.
>>
>You are welcomed.
>>
>Anyway, if you have any concern on this issue, please feel free to let
>me know and I am happy to be of assistance.
>>
>>
>Best regards,
>>
>Peter Huang
>>
>Microsoft Online Community Support
>============== =============== =============== ======
>When responding to posts, please "Reply to Group" via your newsreader
>so that others may learn and benefit from your issue.
>============== =============== =============== ======
>This posting is provided "AS IS" with no warranties, and confers no
>rights.
>>

[rowe_newsgroups]

Re: Excel automation

You may try using the macro recorder function of excel to record the
import, then adapt that code to work with VB.Net. Unfortunately, it's
been to long since I did any excel automation for me to help much more
than that (actually I don't even have excel on this computer).
Hopefully Peter or someone else will read this thread and chip in their
knowledge.

Good Luck!

Seth Rowe


cj wrote:

Yes, writing a CSV file and importing by hand is instantaneous in
comparison. to loading each cell from VB. Now I'd love to know how to
have VB open Excel and import from 1 to 4 files as sheets in a workbook.
I'll look into it some more tomorrow but if you can give me any
pointers I'd appreciate it.
>
cj wrote:

Peter, Here's a question for you. I have approximately 170,000 records
to import into excel (they are in a file formatted as a printable ascii
report now). To get them into excel I see two options. What I'm doing
now is to read each line of the report and for data lines add them to
the excel sheet. When I've added 65000 rows I start adding to a new
sheet. This takes forever. Would it be faster to turn the report into
multiple 65000 record comma delimited files and then import them into
excel? Is it possible to import them automatically?

Peter Huang [MSFT] wrote:

Hi.
>
You are welcomed.
>
Anyway, if you have any concern on this issue, please feel free to let
me know and I am happy to be of assistance.
>
>
Best regards,
>
Peter Huang
>
Microsoft Online Community Support
=============== =============== =============== =====
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=============== =============== =============== =====
This posting is provided "AS IS" with no warranties, and confers no
rights.
>

[Peter Huang [MSFT]]

Re: Excel automation

Hi,

If the 170,000 records are in a DB, we can use the ADO.NET/ADO to tranfer
it into Excel.
Here is a link for your reference.
Transfer Data to a Worksheet by Using ADO.NET
How to transfer data to an Excel workbook by using Visual C# 2005 or Visual
C# .NET
http://support.microsoft.com/default.aspx?scid=kb;[LN];306023


Also the link below is a list of KB which is related with Excel
programming, you may check the Excel section to see what is proper for you.
311452 INFO: Develop Microsoft Office solutions with Visual Studio .NET

http://support.microsoft.com/default.aspx?scid=kb;EN-US;311452

Best regards,

Peter Huang

Microsoft Online Community Support
=============== =============== =============== =====
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=============== =============== =============== =====
This posting is provided "AS IS" with no warranties, and confers no rights.

[cj]

Re: Excel automation

Peter,

Thanks, for the info. The data is not in a DB. I am essentially
reading an ASCII report.

I found it took a very long time to add the data from the report cell by
cell so I changed the program to send the output to a CSV (comma
delimited) file starting a new file each time a file contains 65000
records. It ran in seconds. Then I have to open Excel and starting
with sheet one go to data/Get External Data/Import Text File and import
the first CSV file then select sheet two and following the same steps
import the second CSV file etc. I want the data from each file in
separate sheets in the same Excel workbook.

I briefly looked at your links but from what I saw they can not handle
the importing of multiple CSV files into separate sheets of the same
workbook.

Thanks,
cj



Peter Huang [MSFT] wrote:

Hi,
>
If the 170,000 records are in a DB, we can use the ADO.NET/ADO to tranfer
it into Excel.
Here is a link for your reference.
Transfer Data to a Worksheet by Using ADO.NET
How to transfer data to an Excel workbook by using Visual C# 2005 or Visual
C# .NET
http://support.microsoft.com/default.aspx?scid=kb;[LN];306023
>
>
Also the link below is a list of KB which is related with Excel
programming, you may check the Excel section to see what is proper for you.
311452 INFO: Develop Microsoft Office solutions with Visual Studio .NET

http://support.microsoft.com/default.aspx?scid=kb;EN-US;311452

>
>
Best regards,
>
Peter Huang
>
Microsoft Online Community Support
=============== =============== =============== =====
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=============== =============== =============== =====
This posting is provided "AS IS" with no warranties, and confers no rights.
>

[Michael D. Ober]

Re: Excel automation

Excel itself appears to only be able to load a CSV file into a "new"
workbook. If you need to load multiple CSV files, you're better off writing
EXCEL VBA code to do this or use MS-Access.

Mike Ober.



"cj" <cj@nospam.nosp amwrote in message
news:edhf7GK4GH A.4164@TK2MSFTN GP05.phx.gbl...

Peter,
>
Thanks, for the info. The data is not in a DB. I am essentially reading
an ASCII report.
>
I found it took a very long time to add the data from the report cell by
cell so I changed the program to send the output to a CSV (comma
delimited) file starting a new file each time a file contains 65000
records. It ran in seconds. Then I have to open Excel and starting with
sheet one go to data/Get External Data/Import Text File and import the
first CSV file then select sheet two and following the same steps import
the second CSV file etc. I want the data from each file in separate
sheets in the same Excel workbook.
>
I briefly looked at your links but from what I saw they can not handle the
importing of multiple CSV files into separate sheets of the same workbook.
>
Thanks,
cj
>
>
>
Peter Huang [MSFT] wrote:

>Hi,
>>
>If the 170,000 records are in a DB, we can use the ADO.NET/ADO to tranfer
>it into Excel.
>Here is a link for your reference.
>Transfer Data to a Worksheet by Using ADO.NET
>How to transfer data to an Excel workbook by using Visual C# 2005 or
>Visual C# .NET
>http://support.microsoft.com/default.aspx?scid=kb;[LN];306023
>>
>>
>Also the link below is a list of KB which is related with Excel
>programming, you may check the Excel section to see what is proper for
>you.
>311452 INFO: Develop Microsoft Office solutions with Visual Studio .NET
>http://support.microsoft.com/default...b;EN-US;311452
>>
>>
>Best regards,
>>
>Peter Huang
>>
>Microsoft Online Community Support
>============== =============== =============== ======
>When responding to posts, please "Reply to Group" via your newsreader so
>that others may learn and benefit from your issue.
>============== =============== =============== ======
>This posting is provided "AS IS" with no warranties, and confers no
>rights.
>>

[cj]

Re: Excel automation

How can you say that? I gave the exact steps you can take to import the
CSV file into an existing wookbook in the message you replied to. I
bring each CSV file into a different sheet. Excel is very able to do this.

Michael D. Ober wrote:

Excel itself appears to only be able to load a CSV file into a "new"
workbook. If you need to load multiple CSV files, you're better off writing
EXCEL VBA code to do this or use MS-Access.
>
Mike Ober.
>
>
>
"cj" <cj@nospam.nosp amwrote in message
news:edhf7GK4GH A.4164@TK2MSFTN GP05.phx.gbl...

>Peter,
>>
>Thanks, for the info. The data is not in a DB. I am essentially reading
>an ASCII report.
>>
>I found it took a very long time to add the data from the report cell by
>cell so I changed the program to send the output to a CSV (comma
>delimited) file starting a new file each time a file contains 65000
>records. It ran in seconds. Then I have to open Excel and starting with
>sheet one go to data/Get External Data/Import Text File and import the
>first CSV file then select sheet two and following the same steps import
>the second CSV file etc. I want the data from each file in separate
>sheets in the same Excel workbook.
>>
>I briefly looked at your links but from what I saw they can not handle the
>importing of multiple CSV files into separate sheets of the same workbook.
>>
>Thanks,
>cj
>>
>>
>>
>Peter Huang [MSFT] wrote:

>>Hi,
>>>
>>If the 170,000 records are in a DB, we can use the ADO.NET/ADO to tranfer
>>it into Excel.
>>Here is a link for your reference.
>>Transfer Data to a Worksheet by Using ADO.NET
>>How to transfer data to an Excel workbook by using Visual C# 2005 or
>>Visual C# .NET
>>http://support.microsoft.com/default.aspx?scid=kb;[LN];306023
>>>
>>>
>>Also the link below is a list of KB which is related with Excel
>>programming , you may check the Excel section to see what is proper for
>>you.
>>311452 INFO: Develop Microsoft Office solutions with Visual Studio .NET
>>http://support.microsoft.com/default...b;EN-US;311452
>>>
>>>
>>Best regards,
>>>
>>Peter Huang
>>>
>>Microsoft Online Community Support
>>============= =============== =============== =======
>>When responding to posts, please "Reply to Group" via your newsreader so
>>that others may learn and benefit from your issue.
>>============= =============== =============== =======
>>This posting is provided "AS IS" with no warranties, and confers no
>>rights.
>>>

>
>

[Peter Huang [MSFT]]

Re: Excel automation

Hi CJ,

As you said, if the Data is in the plain text file, you can import it into
Excel as it is a text file.

e.g. the code below will automation Excel to import the two csv file
test.csv and test2.csv into Sheet1 and Sheet2 for your referennce.

Imports Excel = Microsoft.Offic e.Interop.Excel
Module Module1

Sub Main()
Dim oXL As Excel.Applicati on

Dim oWB As Excel.Workbook
' Start Excel and get Application object.
oXL = CreateObject("E xcel.Applicatio n")
oXL.Visible = True
oWB = oXL.Workbooks.A dd
Dim oWS As Excel.Worksheet = oWB.Worksheets( 1)
With oWS.QueryTables .Add(Connection :="TEXT;C:\temp \test.csv",
Destination:=oX L.Range("Sheet1 !A1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFo rmulas = False
.PreserveFormat ting = True
.RefreshOnFileO pen = False
.RefreshStyle = Excel.XlCellIns ertionMode.xlIn sertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWi dth = True
.RefreshPeriod = 0
.TextFilePrompt OnRefresh = False
.TextFilePlatfo rm = 936
.TextFileStartR ow = 1
.TextFileParseT ype = Excel.XlTextPar singType.xlDeli mited
.TextFileTextQu alifier =
Excel.XlTextQua lifier.xlTextQu alifierDoubleQu ote
.TextFileCommaD elimiter = True
.TextFileColumn DataTypes = New Object() {1, 1, 1}
.TextFileTraili ngMinusNumbers = True
.Refresh(Backgr oundQuery:=Fals e)
End With
oWS = oWB.Worksheets( 2)
With oWS.QueryTables .Add(Connection :="TEXT;C:\temp \test2.csv",
Destination:=oX L.Range("Sheet2 !A1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFo rmulas = False
.PreserveFormat ting = True
.RefreshOnFileO pen = False
.RefreshStyle = Excel.XlCellIns ertionMode.xlIn sertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWi dth = True
.RefreshPeriod = 0
.TextFilePrompt OnRefresh = False
.TextFilePlatfo rm = 936
.TextFileStartR ow = 1
.TextFileParseT ype = Excel.XlTextPar singType.xlDeli mited
.TextFileTextQu alifier =
Excel.XlTextQua lifier.xlTextQu alifierDoubleQu ote
.TextFileCommaD elimiter = True
.TextFileColumn DataTypes = New Object() {1, 1, 1}
.TextFileTraili ngMinusNumbers = True
.Refresh(Backgr oundQuery:=Fals e)
End With
End Sub
End Module

Best regards,

Peter Huang

Microsoft Online Community Support
=============== =============== =============== =====
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=============== =============== =============== =====
This posting is provided "AS IS" with no warranties, and confers no rights.