Hello everyone. I'm trying to streamline a database update routine my company has by utilizing some Excel VBA magic. I'd like it to be as idiot proof as possible, with a user just having to select a data file and a template and have the template populated as a result of the code execution.
I have two files (attached) that I need to work with. The file "U_SPAN_Keyboard_Video_Mouse_Switch(KVM)_V2R1_Manual-XCCDF_SCL_Copy" is actually an xml file (extension change due to upload restrictions) and contains the source data. I view this file in Excel by selecting "Open With" using Excel, selecting "Open the file without applying a stylesheet", "As an XML Table" or ", then "OK" to the specified XML or schema source error.
The XCCDF file requires some format changes that I currently carry out through BASH commands. My end goal is to be able to perform the format changes, then call Excel.exe with the STIGImportTemplate.xlsm and an XCCDF file as arguements and have a populated STIGImportTemplate.xlsm saved as the output.
With the XCCDF files... the column assignment for the desired data does not change per XCCDF file, but the beginning and ending rows will change. What needs to happen is all of the cells that contain data in Columns S EXCEPT for the very first row, need to be copied into Column A, Row 2 of the STIGImportTemplate.xlsm. I have been suggested to use the following command to copy all non-blank data from a column but am unsure how to offset this selection by the first row.
Worksheets("Sheet2").Columns("S:S").SpecialCells(xlCellTypeConstants, 23).Copy Destination:=Worksheets("Sheet1").Range("a1")
Once I have that portion figured out, the rest is beyond my understanding of the Excel.exe command line parameters. It is my understanding that macros are basically attached to a worksheet. If that is the case, are either of the following scenarios possible?
1. The macro resides with the STIGImportTemplate.xlsm book, and receives the local path/filename of the workbook to insert as a sheet from a variable passed when excel.exe is called
2. The excel.exe command line string opens a user-specified XCCDF file and somehow the STIGImportTemplate book is inserted as a sheet into the XCCDF book and the macro code is execute to copy the data from XCCDF to template.
Bookmarks