Formatting Excel with VBA - 2 files open

B

BamaColtsFan

Registered User.
Local time
Today, 04:57
Joined
Nov 8, 2006
Messages
91
Greetings!

I am using a VBA module to first export then format a query result from Access to Excel. Before I started trying to format the results, everything worked perfectly. I am able to send my results to Excel without any problems. Now, I'm trying to format my results a little but for some reason, there are two instances of Excel opening when I execute the code. I think it has something to do with how I'm calling the Excel application but I'm not sure. A shortened version of the code is below (only 1 sheet as opposed to the many that I actually export). My question is "Why are there two copies opening?"

Any advice would be deeply appreciated!

Code: 
Public Function CopyToWorkbook()

'  This is the test function to format the output in Excel

Dim db As DAO.Database
Dim newPath As DAO.Recordset

Dim strPath As String

Set db = CurrentDb()
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Set newPath = db.OpenRecordset("Set_Path")

Set ApXL = CreateObject("Excel.Application")
strPath = newPath!Out_Path & "CombinedTimecards_Crosstab.xlsx"

Set xlWBk = ApXL.Workbooks.Open(strPath)
ApXL.Visible = True

     DoCmd.TransferSpreadsheet acExport, 8, "qryFinalCompSum", strPath, True, "Compliance Summary"
     
        Set xlWSh = xlWBk.Worksheets("Compliance_Summary")
        
        xlWSh.Columns("A:E").Select
        
        With ApXL.Selection.Font
        .Name = "Arial"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        End With
        
        xlWSh.Range("A1:E1").Select
        
        With ApXL.Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 16628595
        .TintAndShade = 0
        .PatternTintAndShade = 0
        End With
        
        ApXL.Selection.Font.Bold = True
        
        ApXL.ActiveSheet.Cells.Select

        ApXL.ActiveSheet.Cells.EntireColumn.AutoFit

        xlWSh.Range("A1").Select
        xlWSh.Columns("B:B").Select
        ApXL.Selection.Style = "Percent"
        ApXL.Selection.NumberFormat = "0.0%"
        
        xlWSh.Range("A1").Select
        Exit Function
End Function
 
Would need to see the entire code to find it, but you probably have code which isn't connected to the ApXL object.
 
Hi Bob! Somehow, I just knew this would get your attention! You're always helping me out! Anyway, I tested in my application using this code and the same thing happens. Whatever is wrong is in this block. The other code I pulled out is just me sending out other query results to the Excel workbook (no formatting there yet...). Thanks again for your help!
 
I believe it would be this line:

DoCmd.TransferSpreadsheet acExport, 8, "qryFinalCompSum", strPath, True, "Compliance Summary

That is causing your second instance.
 
boblarson said:
I believe it would be this line:

DoCmd.TransferSpreadsheet acExport, 8, "qryFinalCompSum", strPath, True, "Compliance Summary

That is causing your second instance.
Click to expand...

Well, Bob; you are correct as usual! Taking out that line stops it from opening the extra copy. That means I must have seperate functions to first populate my Excel and then format my results. That will be tricky since I use a loop to build several tabs based on table data, but I think I can work it out. Thanks again for your help!
 
BamaColtsFan said:
Well, Bob; you are correct as usual! Taking out that line stops it from opening the extra copy. That means I must have seperate functions to first populate my Excel and then format my results. That will be tricky since I use a loop to build several tabs based on table data, but I think I can work it out. Thanks again for your help!
Click to expand...

No, you don't need to do that.

Check out my code on my website under Code Snippets. There are some functions you should be able to modify to suit you AND not have to use the TransferSpreadsheet command.
 
Actually, I tried some of those already and the TransferSpreadsheet command seems to work best for me (well, given my clunky coding, it works...). Thus far, I am able to do the formatting in a seperate module and I'm not that bothered by it. What I can't seem to figure out right now is how to get it to move on to the next worksheet once I am done formatting the first. I assumed that simply setting the value of xlWSh to the new worksheet name would do it but that doesn't seem to work... Any ideas?
 
As long as the worksheet exists, you can just set xlWSh to the new sheet, but you have to do it using xlWbk like originally done.
 
Bob,

I'm sorry but I must just be dense... I've tried and tried and I can't get the code to switch to a new worksheet. I am setting the value of xlWBk the same way both initally and where I want to switch. It doesn't change to the new worksheet but it does select all the columns in the Compliance_Summary and I assume it is doing the Autofit again. I just can't seem to find any reference on how to make it move on to the next tab. Latest code is below...

Code: 
Public Function FormatWorkbook()

'  This is the test function to format the output in Excel

Dim db As DAO.Database
Dim newPath As DAO.Recordset

Dim strPath As String

Set db = CurrentDb()
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Set newPath = db.OpenRecordset("Set_Path")

Set ApXL = CreateObject("Excel.Application")
strPath = newPath!Out_Path & "CombinedTimecards_Crosstab.xlsx"

Set xlWBk = ApXL.Workbooks.Open(strPath)
ApXL.Visible = True

     ' DoCmd.TransferSpreadsheet acExport, 8, "qryFinalCompSum", strPath, True, "Compliance Summary"
     
        Set xlWSh = xlWBk.Worksheets("Compliance_Summary")
        
        xlWSh.Columns("A:E").Select
        
        With ApXL.Selection.Font
        .Name = "Arial"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        End With
        
        xlWSh.Range("A1:E1").Select
        
        With ApXL.Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 16628595
        .TintAndShade = 0
        .PatternTintAndShade = 0
        End With
        
        ApXL.Selection.Font.Bold = True
        
        ApXL.ActiveSheet.Cells.Select

        ApXL.ActiveSheet.Cells.EntireColumn.AutoFit

        xlWSh.Range("A1").Select
        xlWSh.Columns("B:B").Select
        ApXL.Selection.Style = "Percent"
        ApXL.Selection.NumberFormat = "0.0%"
        
        xlWSh.Range("A1").Select

        ' This is were it should move to the next worksheet but doesn't
        
        Set xlWSh = xlWBk.Worksheets("Delinquent_List")
        
        ApXL.ActiveSheet.Cells.Select

        ApXL.ActiveSheet.Cells.EntireColumn.AutoFit

        ' xlWSh.Range("A1").Select
        
        Exit Function
                
End Function
 
It IS setting the worksheet to that. BUT you aren't using it, nor have you set it as the active worksheet. That is why the code is failing.

If you are going to use the ApXL.ActiveSheet...etc. Then you would need to have a

xlWSh.Select

to make it the active sheet.
 
Not trying to butt in but you can do both. Sample code showing how you can format an Excel file after you run your TransferSpreadsheet...

Code: 
Public Sub Testing()
 
     DoCmd.TransferSpreadsheet acExport, 8, "qryFinalCompSum", strPath, True, "Compliance Summary"
 
     Call ModifyExcelFile(strPath)
 
End Sub

Code: 
Public Sub ModifyExcelFile(sPathFile As String)

    Dim xlApp As Object
    Dim xlSheet As Object

    Set xlApp = CreateObject("Excel.Application")
    Set xlSheet = xlApp.Workbooks.Open(sPathFile).Sheets(1)
    
    With xlApp
            .Application.Sheets("qryFinalCompSum").Select
            .Application.Sheets("qryFinalCompSum").Name = "Final Comp Sum"
            .Application.Cells.Select
            .Application.Selection.ClearFormats
            .Application.Selection.Font.Name = "Arial"
            .Application.Rows("1:1").Select
            .Application.Selection.Font.Bold = True
            .Application.Range("J:T,V:V,W:W,Y:AA").Select
            .Application.Selection.NumberFormat = "mm/dd/yyyy"
            .Application.Cells.Select
            .Application.Selection.RowHeight = 12.75
            .Application.Selection.Columns.AutoFit
            .Application.Range("B2").Select
            .Application.ActiveWindow.FreezePanes = True
            .Application.Range("A1").Select

            .Application.ActiveWorkbook.Save
            .Application.ActiveWorkbook.Close
            .Quit
    End With

    Set xlApp = Nothing
    Set xlSheet = Nothing
    
End Sub
 
Ok, I think I understand what you mean. Now, how would you format a second, different worksheet? I can do the formatting just fine, I just can't get it to move to the next sheet....
 
BamaColtsFan said:
Ok, I think I understand what you mean. Now, how would you format a second, different worksheet? I can do the formatting just fine, I just can't get it to move to the next sheet....
Click to expand...

You just have to select the other worksheet...

Code: 
    With xlApp
            .Application.Sheets("qryFinalCompSum").Select
            .Application.Sheets("qryFinalCompSum").Name = "Final Comp Sum"
            .Application.Cells.Select
            .Application.Selection.ClearFormats
            .Application.Selection.Font.Name = "Arial"
            .Application.Rows("1:1").Select
            .Application.Selection.Font.Bold = True
            .Application.Range("J:T,V:V,W:W,Y:AA").Select
            .Application.Selection.NumberFormat = "mm/dd/yyyy"
            .Application.Cells.Select
            .Application.Selection.RowHeight = 12.75
            .Application.Selection.Columns.AutoFit
            .Application.Range("B2").Select
            .Application.ActiveWindow.FreezePanes = True
            .Application.Range("A1").Select

[B]            [COLOR="Blue"].Application.Sheets("AnotherSheet").Select[/COLOR][/B]
            .Application.Cells.Select
            .Application.Selection.ClearFormats
            .Application.Selection.Font.Name = "Arial"
            .Application.Rows("1:1").Select
            .Application.Selection.Font.Bold = True

[B]            [COLOR="blue"].Application.Sheets("AndAnotherSheet").Select[/COLOR][/B]
            .Application.Cells.Select
            .Application.Selection.ClearFormats
            .Application.Selection.Font.Name = "Arial"
            .Application.Rows("1:1").Select
            .Application.Selection.Font.Bold = True

            .Application.ActiveWorkbook.Save
            .Application.ActiveWorkbook.Close
            .Quit
    End With
 
Ok... I've got ghudson's method working pretty well for my purpose except that I'm having a little trouble with a loop. I'm sure this is a very elementary mistake but I can't figure out how to make it work. What I need to do is use a DO UNTIL loop to go through a list of codes that eventually become sheet names in the Excel Workbook. The code below gives an error of "Subscript out of range" on .Application.Sheets(" & newCMD & ").Select. I think it has to do with calling the variable newCMD into the code since it works perfectly if I remove the loop and specify a sheet name. So my question is, how do you correctly call the variable in this case?


Code: 
Do Until myCMD.EOF

newCMD = myCMD!CMD_Code

            .Application.Sheets(" & newCMD & ").Select
            .Application.Cells.Select
            .Application.Selection.ClearFormats
            .Application.Selection.Font.Name = "Arial"
            .Application.Selection.Font.Size = 10
            .Application.Range("J:K").Select
            .Application.Selection.NumberFormat = "[$-409]d-mmm-yy;@"
            .Application.Range("A1:L1").Select
            .Application.Selection.Font.Bold = True
            .Application.Selection.Interior.Pattern = xlSolid
            .Application.Selection.Interior.Color = 16628595
            .Application.Selection.Interior.TintAndShade = 0
            .Application.Selection.Interior.PatternTintAndShade = 0

            .Application.Range("A:L").Select
            .Application.Selection.Columns.AutoFit
            
            .Application.Range("A1").Select

myCMD.MoveNext

Loop
 
UGH!!!! I'm sooooo stupid!

All I had to do was replace

.Application.Sheets(" & newCMD & ").Select

with

.Application.Sheets(newCMD).Select

and it works great! Sorry to be such a bother...
 
BamaColtsFan said:
Sorry to be such a bother...
Click to expand...
It isn't a bother. That's why we're all here; to learn and help each other. Sometimes you wind up helping yourself, but by placing the answer here you just might help someone else down the line.

So, no bother whatsoever. :)
 
You must log in or register to reply here.

Similar threads

Gasman
Solved Cannot see the wood for the trees :(
Replies
15
Views
1,296
Gasman
Gasman
J
VBA to print Word file and close is
Replies
8
Views
702
DickyP
DickyP
Gasman
Solved Open Excel workbook, but have WorkBook_Open code run
Replies
19
Views
1,820
Gasman
Gasman
N
Solved How get lid of domain function and replace them with opening recordset in MS Access VBA
Replies
10
Views
506
nector
N
B
Access form in Office 365 is minimized when opening from Excel using Docmd.Openform
Replies
15
Views
747
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom