Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mcrors_calhoun
Starting Member
22 Posts |
 Posted - 2007-09-13 : 12:34:42
|
| Hi,I have an excel sheet that comes into a specified folder each month. I have a DTS package that will load this excel data into my DB. Before the excel sheet is loaded I need to do some automated reformatting. I have developed some macros to do this reformatting. The problem is that the macro is attached to my test/template excel doc only. Is there some way that I can open this test/template excel doc and copy the macro from this doc into my monthly input excel doc, and then run the macro on the monthly input excel doc. I have run macros on excel sheets through ActiveX task before, but I have never had to copy a Macro from one excel doc to another and I am not sure if it is possible. Any help would be much appreciated.ThanksMcRors |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-13 : 23:31:05
|
| Or you can load data into staging table and do reformatting there. |
 |
|
|
mcrors_calhoun
Starting Member
22 Posts |
Posted - 2007-09-14 : 11:54:28
|
| Well I am doing some reformating later in the load process through staging tables alright but the original Excel sheet is pretty rough and the easiest way to tidy it up is using the Macro, but only if it is possible to apply the Macro to a different excel sheet, or perhaps I could take the VB code in the macro and apply it from an ActiveX task in DTS? |
|
|
|
nkarthick
Starting Member
3 Posts |
Posted - 2007-09-17 : 08:29:51
|
| hi, Better to use a generic XL addin instead of template, and placed this addin in 'C:\Program Files\Microsoft Office\OFFICE11\XLSTART' Note: the directory will vary upon the MS-Office versionRegards,Narayana Karthick(VBA Developer) |
|
|
|
mcrors_calhoun
Starting Member
22 Posts |
Posted - 2007-09-17 : 10:52:49
|
| Cheers,I have created an excel sheet called personal.xls and placed it into the folder that you specified. I can now run this macro on any excel document that I open. I am trying to automate this through an ActiveX task in DTS, the code that I am using is Set objExcel = CreateObject("EXCEL.APPLICATION") objExcel.Workbooks.Open fullfilename objExcel.Run "personal.xls!reformat" objExcel.ActiveWorkbook.Save objExcel.ActiveWorkbook.Close True objExcel.Quit Set objExcel = Nothing Where fullfilename is the location of my input folder plus the file that is being processed. But I am getting an error which says:"'personal.xls' could not be found."Does this file need to be in the same location as the file being processed?Thanks |
|
|
|
|
|
|
Applying Macros through DTS package