Excel automation

  • Thread starter Thread starter cj
  • Start date Start date
C

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.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet

oXL = CreateObject("Excel.Application")
oXL.Visible = True

oWB = oXL.Workbooks.Add
 
I would check out the microsoft.public.excel.programming 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.ActiveWorkbook.Sheets("Sheet1").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
 
Hi

We can use the Automation code as below to delete the another two sheets.
Imports Excel = Microsoft.Office.Interop.Excel
Module Module1
Sub Main()
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
oXL = CreateObject("Excel.Application")
oXL.Visible = True
oWB = oXL.Workbooks.Add
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.public.office.developer.automation
microsoft.public.excel.programming

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.
 
Thanks

rowe_newsgroups said:
I would check out the microsoft.public.excel.programming 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.ActiveWorkbook.Sheets("Sheet1").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
Click to expand...
 
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, 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?
 
If you can hit the data with SQL statements you could use excel's
querytables to do a much faster import.

Thanks,

Seth Rowe
 
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.
 
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
 
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.
 
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
 
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 said:
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


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.
Click to expand...
Click to expand...
 
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.
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 said:
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


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.
Click to expand...
Click to expand...
Click to expand...
 
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.Office.Interop.Excel
Module Module1

Sub Main()
Dim oXL As Excel.Application

Dim oWB As Excel.Workbook
' Start Excel and get Application object.
oXL = CreateObject("Excel.Application")
oXL.Visible = True
oWB = oXL.Workbooks.Add
Dim oWS As Excel.Worksheet = oWB.Worksheets(1)
With oWS.QueryTables.Add(Connection:="TEXT;C:\temp\test.csv",
Destination:=oXL.Range("Sheet1!A1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = Excel.XlCellInsertionMode.xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 936
.TextFileStartRow = 1
.TextFileParseType = Excel.XlTextParsingType.xlDelimited
.TextFileTextQualifier =
Excel.XlTextQualifier.xlTextQualifierDoubleQuote
.TextFileCommaDelimiter = True
.TextFileColumnDataTypes = New Object() {1, 1, 1}
.TextFileTrailingMinusNumbers = True
.Refresh(BackgroundQuery:=False)
End With
oWS = oWB.Worksheets(2)
With oWS.QueryTables.Add(Connection:="TEXT;C:\temp\test2.csv",
Destination:=oXL.Range("Sheet2!A1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = Excel.XlCellInsertionMode.xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 936
.TextFileStartRow = 1
.TextFileParseType = Excel.XlTextParsingType.xlDelimited
.TextFileTextQualifier =
Excel.XlTextQualifier.xlTextQualifierDoubleQuote
.TextFileCommaDelimiter = True
.TextFileColumnDataTypes = New Object() {1, 1, 1}
.TextFileTrailingMinusNumbers = True
.Refresh(BackgroundQuery:=False)
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.
 
Back
Top