473,287 Members | 1,865 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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

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

oWB = oXL.Workbooks.Add
Sep 18 '06 #1
14 6846
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
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.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet

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

oWB = oXL.Workbooks.Add
Sep 18 '06 #2
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.

Sep 19 '06 #3
cj
Thanks

Peter Huang [MSFT] wrote:
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.
Sep 19 '06 #4
cj
Thanks

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

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

oWB = oXL.Workbooks.Add
Sep 19 '06 #5
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.

Sep 20 '06 #6
cj
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.
Sep 20 '06 #7
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.
Sep 20 '06 #8
cj
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.
Sep 20 '06 #9
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.
Sep 20 '06 #10
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.

Sep 21 '06 #11
cj
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.
Sep 25 '06 #12
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.nospamwrote in message
news:ed**************@TK2MSFTNGP05.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.

Sep 25 '06 #13
cj
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.nospamwrote in message
news:ed**************@TK2MSFTNGP05.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.

Sep 25 '06 #14
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\test 2.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.

Sep 26 '06 #15

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

17
by: Ange T | last post by:
Hi there, I'm having pain with the VB behind an Access form. The form is used to create reports in Excel based on the details entered in the form. This has always worked without error on my...
2
by: jeffgeorge | last post by:
I'm currently exporting a form to Excel. Because there are controls and totals in the header, I first have a button for users to convert to a datasheet. Then I use the automated quick office...
7
by: taylor.bryant | last post by:
I am running: Win XP SP2 Excel 2002, Access 2002 (Office XP SP3) Using Visual Basic (not VB.NET) At one point (prior to XP SP2?!? - I can't pin it down), this did not happen and I was easily...
1
by: cybertof | last post by:
Hello, Is there a way to connect (through automation) a c# application to a running Excel 2003 instance on a specific workbook ? In the past, i used to use GetObject(...) function in VB6. ...
17
by: Mansi | last post by:
I need to do some research on how to use excel automation from c#. Does anyone know of any good books related to this subject? Thanks. Mansi
12
by: D. Shane Fowlkes | last post by:
This most likely belongs in another forum but I thought I'd start here. I have a COM Object written in VB6. The DLL will access MS Excel and use it's Object Library to write a customized report...
3
by: Carlos Magalhaes | last post by:
Hey All, I am doing some excel automation using the excel COM. I can do most of the functions and its working well until I come across a formula. I can run a formula and insert the formula...
3
by: Mitchell Vincent | last post by:
Does anyone have some good examples of Excel automation with (VB).NET? I have some Excel spreadsheets that a customer needs parsed out but I've never tried to use Excel programatically before! ...
6
by: a.theil | last post by:
Please help! I need a simple excel automation, just 2 write some files into excel. I do: Dim oXL As Excel.Application Dim oWB As Excel.Workbook Dim oSheet As Excel.Worksheet Dim oRng As...
1
by: Troy | last post by:
I have VB.Net code that opens Excel files and imports data from them. It works fine for all versions up to 2003 and has for over a year. We have users updating to Office 2003 and the Excel...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.