Excel VBA Apply Formatting to a Different Workbook

Thank you for looking at my question,

I have a macro-enabled workbook 'Process Data.xlsm' that when the user clicks a button, reads data line by line from a text file 'Data_Out.csv' carries out some calculations and writes the results to a worksheet 'Period Issue.xlsx'

After the processing is complete I want to make the Period Issue.xlsx workbook a bit more presentable - autofit columns, coloured headings etc.

The code I have writes the data to the file correctly but then applies the formatting to the macro-enabled workbook and not the target workbook.

Please, how do I maintain the target workbook as the active workbook in order to apply the pretty formatting?



[/Sub Item_Process()

Dim MyExcelFilePath As String
Dim MyTextFilePath As String

Application.DisplayAlerts = False
On Error Resume Next

    ' Set a string to the path of the Excel File
    MyExcelFilePath = "C:\Period Issue\Period Issue.xlsx"

    ' Open the spreadsheet using the Excel Application Object
    Set ExcelObject = CreateObject("Excel.Application")
    ExcelObject.Workbooks.Open MyExcelFilePath

    ' Set the Active Worksheet (in this case the first sheet)
    Set SheetObject = ExcelObject.ActiveWorkbook.Worksheets("Sheet1")

'' Declare a constant that can be used throughout the script
    Const ForReading = 1

' Set a string to the path of the text file
    MyTextFilePath = "C:\Period Issue\PeriodIssueData_Out.csv"

' Set the use of the FileSystemObject so we can open and read the text file
    Set FileSystemObject = CreateObject("Scripting.FileSystemObject")

' Set the text file to read and open it in read-only mode
    Set MyTextRead = FileSystemObject.OpenTextFile(MyTextFilePath, ForReading)

'' Loop through the text file until we reach the bottom of it
    Do Until MyTextRead.AtEndOfStream
        
	''''''Data manipulation code here'''''

    Loop
    
    ExcelObject.ActiveWorkbook.Save
    
    With SheetObject
        .Columns("A:C").EntireColumn.AutoFit
        Range("A1:Q2").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent5
            .TintAndShade = 0.599963377788629
            .PatternTintAndShade = 0
        End With
    End With   
    
    Application.DisplayStatusBar = False
    
    MsgBox "Period Issue Data Saved to C:/Period Issue/Period Issue.xlsx", vbOKOnly, "Processing Complete"
    
'' Save
ExcelObject.ActiveWorkbook.Save
ExcelObject.ActiveWorkbook.Close
ExcelObject.Application.Quit
    
Close

End Subcode

Open in new window

Process-Period-Issue-Data.xlsm
Gary CroxfordOperations Support AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
HI,

pls try

       
        With .Range("A1:Q2").Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent5
            .TintAndShade = 0.599963377788629
            .PatternTintAndShade = 0
        End With

Open in new window

Regards
Gary CroxfordOperations Support AnalystAuthor Commented:
Rgonzo1971,

Returns a Compile Error - Invalid or Unqualified Reference
Rgonzo1971Commented:
SheetObject

then try

     With SheetObject.Range("A1:Q2").Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent5
            .TintAndShade = 0.599963377788629
            .PatternTintAndShade = 0
        End With

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gary CroxfordOperations Support AnalystAuthor Commented:
Excellent, thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.