Hi,

I am trying to load several excel-spreadsheets via DTS.
My problem is, that I need a routine, which can transpose
the excel-sheets in the DTS codebefore loading, like the
transpose command in Excel.
The data, which should be loaded, is different in each
excel-workbook, so that a static code, which looks to a
special row or column wouldn't solve the problem.

Do you have an idea, how to solve this issue?

Thanks in advance!

Best regards,
Dirk Grunewald

Hi Dirk,

Unfortunately, there is no built-in function of DTS available to transpose
the excel-sheets. Also, we do not know what columns and rows the
excel-sheets have. It is difficult and complex to code the routine. I
suggest we transpose the excel-sheets in Excel and import it to the SQL
Server via DTS. I think this is a better choice. Please feel free to
contact me if you have further questions.

Thanks for using MSDN newsgroup.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Hi Michael,

thanks for your answer!

We want to use this routine on a database server. My
concern is, that using an excel VBA macro on a server is
not as stable as using a DTS routine.
Is there a stable solution to run an excel VBA macro via
DTS coding?

Thanks in advance!

Dirk

Hi Dirk,

Thanks for your feedback about our newsgroup. We can run an excel VBA macro
via DTS coding. In Data Transformation Services (DTS), you can extend the
capabilities of your DTS package by using Microsoft? ActiveX? scripts that
implement the objects, properties, methods, and collections of the DTS
object model.

Here is an example regarding running excel VBA macro via DTS coding.

Function Main()

Dim oExcel
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open "d:\Test.xls", False, True
oExcel.run "Macro1"
oExcel.Quitset oExcel = nothing
Main = DTSTaskExecResult_Success

End Function

Macro1 is the VBA macro defined in excel.

For additional information regarding ActiveX scripts in DTS, please refer
to the following article on SQL Server Books Online.
Topic: "Using ActiveX Scripts in DTS"

I hope this explanation is clear. Please let us know if you need further
assistance on this issue.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Hi Dirk,

Server-side automation of Office is not recommended as it is not designed
to do so.

Developers can use Automation to Microsoft Office to build custom solutions
that utilize the capabilities and features that are built into the Office
product. While such programmatic development can be implemented on a client
system with relative ease, there are a number of complications that can
occur if Automation is to take place from server-side code such as Active
Server Pages (ASP), DCOM, or an NT Service.

This article discusses the complications that developers may face, offers
alternatives to Automation that can speed performance, and suggests ways to
configure Office if server-side Automation is unavoidable. Developers
should be aware, however, that the suggestions provided below are for
informational purposes only. Microsoft does not recommend or support
server-side Automation of Office.

257757 INFO: Considerations for Server-Side Automation of Office
http://support.microsoft.com/?id=257757

Thanks for using MSDN newsgroup.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Hi Michael,

due to the difficulties with server-side office
implementation, I would like to give DTS another try.
My idea is to copy the data from the spreadsheet in a two
dimensional array in memory and then writing the data in
the new table. The transposion should be possible, if the
two dimensions are changed during this process.

Do you have an example, how such a process could be
developed in DTS?

Thanks in advance!

Dirk Grunewald

Hi Dirk,

Thanks for your feedback. As I understand, you want to transpose a sheet
and transfer it into SQL Server via DTS. If I have misunderstood, please
feel free to let me know.

DTS is designed to transform data and there is no built-in function of DTS
available to transpose the excel-sheets. However, we could try to create an
.exe file to transpose the sheet of the Excel file via automation.
According to my experience, it is better not to perform the .exe file in
the DTS package. We can perform the .exe file in the scheduled job and
transform the transposed sheet into SQL Server via DTS.

For additional information regarding this issue, please refer to the
following articles on the SQL Server Books Online.
Topic: "Creating SQL Server Agent Jobs"
Topic: "How to create a CmdExec job step (Enterprise Manager)"

Please feel free to let me know if this solves your problem or if you would
like further assistance.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Hi Michael,

thanks for this information.

Actually, we are developing the descriped .exe file for
transposing the workbooks.

For us, it would be helpful, if you could provide some
sample code for opening an excel-file in VB.Net.
Especially, which is the best way to open this workbook
(ODBC, etc)?

Thanks in advance!

Regards,
Dirk Grunewald

Hi Dirk,

Thanks for your response. I think the following information will help you a
lot.

This article demonstrates how to create an Automation client for Microsoft
Excel by using Microsoft Visual Basic .NET.
301982 HOWTO: Automate Microsoft Excel from Visual Basic .NET
http://support.microsoft.com/?id=301982

This step-by-step article describes how to automate Microsoft Excel from
Microsoft Visual Basic .NET to create a workbook that contains a new macro
that is associated with a CommandBar button.
303871 HOW TO: Create an Excel Macro by Using Automation from Visual Basic
.NET
http://support.microsoft.com/?id=303871

This step-by-step article describes several methods for transferring data
to Excel 2002 from a Visual Basic .NET program. This article also presents
the advantages and disadvantages of each method so that you can select the
solution that works best for your situation.
306022 HOW TO: Transfer Data to an Excel Workbook by Using Visual Basic .NET
http://support.microsoft.com/?id=306022

This step-by-step article describes how to call Office macros from a Visual
Basic .NET Automation client. You can use Microsoft Office Automation to
open a document or create a new document that contains a Visual Basic for
Applications (VBA) macro and execute the macro at run time.
306682 HOWTO: Run Office Macros by Using Automation from Visual Basic .NET
http://support.microsoft.com/?id=306682

As to the best way to open the workbook, it always depends on your
situation. We are unable to pinpoint exactly what method is the best
without considering the circumstances under which you are operating.

Please feel free to post in the group if this solves your problem or if you
would like further assistance.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.