just keep building the string with any char that is not strikethrough String CleanStrikethroughChars(Excel.Range range) now you can replace the cell value with the clean_string value String clean_string = CleanStrikethroughChars(Excel.Range range) set a range equal to a single cell that has your text that needs cleanedĮxcel.Range range = worksheet.get_Range("somecell", "somecell") Then, open your Excel menu by right-clicking anywhere within your selected cells. Choose the cell (or cells) you want to apply the strikethrough effect to. One way to do this is by using your right-click menu.
open some workbook and get a spreadsheet.Įxcel.Application app = new Excel.Application() Įxcel.Workbook workbook = ("workbook name",Įxcel.Worksheet ws = (Excel.Worksheet)app.Sheets You can also use the strikethrough formatting option on your cells by bypassing the Home tab completely. And, at the very least, it shows the C# syntax to detect strike-through as a starting point for you. I'm sure there is a more eloquent solution but this works. ObjExcelBook.SaveAs filepath & "\Output\" & CELLA1 & ".Not sure but I thought you were looking for a C# solution. 'Only want the worksheets that are DM (GET or GROW) and are not Cancelled Set objSheet = objExcelBook.Sheets( sName ) =false ' Turn off security alertyįor e = 1 to ' Loop Through all worksheetsĭim sName : sName = objExcelBook.Worksheets( e ).Name Set objExcel = CreateObject("Excel.application") ' create an excel object Set objFile = FSO.GetFile(objArgs(I)) 'Full Path and file nameīasename = FSO.GetBaseName(objfile) 'base file name without path and extensionĮxtension = FSO.GetExtensionName(objfile) 'file name extensionįilepath = FSO.GetParentFolderName(objFile) 'file path without trailing slashįileName = FSO.GetFileName(objFile) 'file name with extension Set FSO = CreateObject("Scripting.FileSystemObject") Click the worksheet location where you want the upper-left corner of the label to appear. And if you see anyway I can improve this Vbscript let me know. Add a label (ActiveX control) Click Developer and then click Insert, and under ActiveX Controls, click Label. If anyone can look at the Macro and figure out how I can integrate it into this whole process I would appreciate it. One thing I noticed with the Macro is if I select a range of cells with it and one of those cells is just a number I get an error and the macro stops running.
It outputs the needed worksheets to another folder and another process watches that and kicks off the SAS program. Client drops the file into a folder on our ftp site and we have a process that watches that folder and pulls down the XLSX file and kicks off my vbscript to run it. We receive an updated schedule weekly and currently we have this whole process completely automated. No button or an option is there on the ribbon. When it comes to Excel, we don’t have any direct option to apply strikethrough to a cell. In Microsoft Word, the option for striking through a text is there on the home tab of the ribbon. Now my issue is how can I automate this from within my vbscript. It means to draw a line through a value in a cell. Now I found a Macro where I can select a range of Cells and run the macro to remove any text with a StrikeThrough effect. Now one of my users has requested if we can remove any text that has the StrikeThrough effect from the excel file before it is saved as the tab delimited file. We use the tab delimited files as input to a SAS program. The workbook has dozens of worksheets and currently I have a vbscript that saves only the worksheets I need as tab delimited files. We get a schedule from a client that comes in an XLSX file format.
0 kommentar(er)
