I'm currently making a vb.net program that will run several queries, and the results of each will automatically be put into worksheets in a new Excel workbook (i.e. one set of results will go into a new worksheet, the next set will go into a different worksheet, etc.).
I'm now trying to iterate though each of these worksheets and make formatting changes to them. The problem I'm having is related to Freezing Panes. My original idea to perform the freeze was to manually select a cell in the row I want to freeze (i.e. Range("A8").select), and then use "Application.ActiveWindow.
s = true". Whats interesting is that this does work, but only for the first worksheet in my workbook. As soon as my program tries to select cell "A8" on any other sheet, it throws an error.
I've also tried doing this (oSheet is the current worksheet I'm iterating through):
This doesn't throw an error (I'm not selecting a cell, technically), but it only freezes the panes on the first worksheet again. This is peculiar, because I am able to make other formatting changes (borders, fonts, change text in cells, page setups, etc.) to every other worksheet using my current method.
This is the code for applying the style changes to each worksheet (the applymacro function does all of the work).
For counter = 0 To SheetTitles.Count - 1
stdSheet = CType(xlSheets.Item(counter + 1), Excel.Worksheet)
stdSheet.Name = SheetTitles((CountOfSheetsNeeded - 1) - counter)
xlCells = stdSheet.Cells
GenerateExcelFile(dsHolder((CountOfSheetsNeeded - 1) -counter).tables(0), xlCells) 'Fill in the data
ApplyMacro header and line I'm getting an error on:
Private Sub ApplyMacro (ByVal oSheet as Excel.Worksheet)