从SQL SVR 2012自动导出表/查询到Excel 2007和后续VBA格式宏？

I’ve been given the task of updating some code which isn’t working after our SQL SVR 200 to SQL SVR 2012 (don't laugh) conversion. We have an automated task that creates 500ish xls files in a line-by-line manner by using the old sp_OACreate command. In 2000, this job always takes hours and is rickety at best. Needless to say, it doesn’t work at all in 2012. That's what you get for never upgrading?

我被赋予了更新一些代码的任务,这些代码在我们的SQL SVR 200到SQL SVR 2012(不要笑)转换之后无效。我们有一个自动化任务,通过使用旧的sp_OACreate命令以逐行方式创建500个xls文件。在2000年,这项工作总是花费数小时,充其量也是摇摇晃晃的。毋庸置疑,它在2012年根本不起作用。那就是你从未升级过的东西?

I rewrote the job by constructing a table, adding info into the table, & doing a bulk export into xls (using OpenRowSet). The new task ran in less than 15 minutes and I was ecstatic. Then Ops complained that the new files weren’t formatted…

我通过构造一个表,向表中添加信息,以及批量导出到xls(使用OpenRowSet)重写了这个工作。新任务在不到15分钟的时间内完成,我欣喜若狂。然后Ops抱怨新文件没有格式化......

I looped back and tried to get the formatting into the automation. That’s where everything fell apart.

我回过头来试图将格式化到自动化中。一切都崩溃了。

1. Use OpenRowSet and export into an “xls template file” that has an autorun vba code to do the formatting when the file is opened for the first time. Didn’t work.

   使用OpenRowSet并导出到具有自动运行vba代码的“xls模板文件”,以便在首次打开文件时进行格式化。没工作。

2. Use OpenRowSet and export into an “xlsm template file” that has an autorun vba code to do the formatting when the file is opened for the first time. Didn’t work.

   使用OpenRowSet并导出到具有自动运行vba代码的“xlsm模板文件”,以便在首次打开文件时进行格式化。没工作。

3. Use BCP and export into an “xls template file” that has an autorun vba code to do the formatting when the file is opened for the first time. Didn’t work.

   使用BCP并导出到具有自动运行vba代码的“xls模板文件”,以便在首次打开文件时进行格式化。没工作。

4. Use BCP and export into an “xlsm template file” that has an autorun vba code to do the formatting when the file is opened for the first time. Didn’t work.

   使用BCP并导出到具有自动运行vba代码的“xlsm模板文件”,以便在首次打开文件时进行格式化。没工作。

5. Use BCP/OpenRowSet and export into xls/xlsx and try to execute a vba code from another workbook…Didn’t work.

   使用BCP / OpenRowSet并导出到xls / xlsx并尝试从另一个工作簿执行vba代码...不起作用。

6. Use BCP/OpenRowSet, export into a “dummy” xls/xlsx, use a bat file to recopy the file, resave, etc…Didn’t work.

   使用BCP / OpenRowSet,导出到“虚拟”xls / xlsx,使用bat文件重新复制文件,重新保存等...不起作用。

7. Use BCP/OpenRowSet, export into a “dummy” txt/csv, use a bat file to convert the file into excel, resave, etc…Didn’t work.

   使用BCP / OpenRowSet,导出到“虚拟”txt / csv,使用bat文件将文件转换为excel,重新保存等...不起作用。

8. Etc. Didn't work.

   等等。没有用。

I tried every combination, every export method, every filetype, etc. Certain methods allowed me to export but no formatting. Other methods wouldn’t even export anything. No method permitted the export/format, though.

我尝试了每个组合,每个导出方法,每个文件类型等。某些方法允许我导出但没有格式化。其他方法甚至不会导出任何东西。但是,没有方法允许导出/格式化。

Then I discovered the “REAL” problem ==> When I export using BCP/OpenRowSet and then try to open the file, I always get the “file you are trying to open is in a different format than specified by the file extension” error (FYI Using Excel 2007). I had sort of ignored this error, but after days of banging my head against the wall I can now see that this error was leading me to the real problem all along. The export file (regardless of xls/xlsm/xlsx) is not an actual excel file; it’s really just a bunch of html tags. This is why the formatting vba code won’t work; it’s not an actual excel file! No matter what I do, it’s still not a real excel file. Formatting will never work because it's simply not a real excel file.

然后我发现了“REAL”问题==>当我使用BCP / OpenRowSet导出然后尝试打开文件时,我总是得到“你试图打开的文件格式与文件扩展名指定的格式不同”错误(仅供参考,使用Excel 2007)。我有点忽略了这个错误,但是经过几天把我的头碰到墙上,我现在可以看到这个错误导致我一直遇到真正的问题。导出文件(不管xls / xlsm / xlsx)都不是实际的excel文件;它实际上只是一堆html标签。这就是为什么格式化vba代码不起作用的原因;它不是一个真正的excel文件!无论我做什么,它仍然不是一个真正的excel文件。格式化永远不会起作用,因为它根本不是真正的excel文件。

So, I need an automated method to export a table to some sort of excel format (xls/xlsm/xlsx) and then execute a vba code to format (bold, column width, number/date formatting, etc.) the newly-exported file. This seems like such a routine task…but I see now that routine <> easy. I’ve seen references to NPOI and ClosedXML in forums, but I simply can’t believe that I need additional 3rd party software to accomplish this task.

所以,我需要一个自动方法将表导出为某种excel格式(xls / xlsm / xlsx),然后执行vba代码格式化(粗体,列宽,数字/日期格式等)新导出的文件。这似乎是一项例行任务......但我现在看到常规<>很容易。我在论坛上看过对NPOI和ClosedXML的引用,但我简直无法相信我需要额外的第三方软件来完成这项任务。

1 个解决方案

#1

You could write some VBA code in an Excel Spreadsheet to go get the data from your database and then do the formatting VBA after. The below code should be a good starting point.

您可以在Excel电子表格中编写一些VBA代码,以从数据库中获取数据,然后执行格式化VBA。以下代码应该是一个很好的起点。

Please note that you will have to add a Reference to Microsoft ActiveX Data Objects Library for this code to function. Also, you may need to change the Provider in you connection string deepening on you systems configuration. The below code utilizes the SQL Server Native Client 11.

请注意,您必须添加对Microsoft ActiveX数据对象库的引用才能使此代码正常运行。此外,您可能需要在系统配置中更改连接字符串深化的提供程序。以下代码使用SQL Server Native Client 11。

Dim cn As New ADODB.Connection
Dim SQLrs As New ADODB.Recordset
Dim SQLCommand As String

SQLCommand = "SELECT...FROM..."

cn.ConnectionString = "Provider=SQLNCLI11;Server=xxxxxxx;DataBase=xxxxxx;Trusted_Connection=yes;"
cn.Open
cn.Execute "SET NOCOUNT ON" ''required for complex T-SQL queries

SQLrs.CursorLocation = adUseClient
Call SQLrs.Open(SQLCommand, cn, adOpenStatic, adLockBatchOptimistic)

ActiveWorkbook.Sheets("sheet1").Range("a1").CopyFromRecordset SQLrs

SQLrs.Close
Set SQLrs = Nothing
cn.Close
Set cn = Nothing