Access to Excel Compatability

B

BamaColtsFan

Registered User.
Local time
Today, 17:43
Joined
Nov 8, 2006
Messages
91
Ok, I am using Office 2007 to build an Access application that uses VBA to first create and then format an Excel file. Everything works fairly well despite my very clunky coding except when I get to the end of the formatting function. I am getting an error from "Compatibility Checker" that says basically my file is not in the expected format and I may lose some formatting if I save it. I am pretty sure this is related to the fact that I have to use a .xls extension as opposed to a .xlsx in order for the formatting code to work correctly.

So, my question is this: Can I suppress that message or otherwise save the file without triggering the check on the format?
 
Sounds like you are formatting the file with options only allowed in the 2007 version of Excel yet you are trying to save the file in the 2003 .xls version of excel. Alter your formatting code to only apply formatting that is compatable with the 2003 version of excel.
 
Well, I really thought I had done that... I know it's kind of lengthy but I am posting the formatting code below. I guess I don't know what might be a 2007 format feature based on what I am doing, but I admit I am not that good at VBA, so....

Code: 
Option Compare Database

Public Function ConverToFormattedWorkbook()

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

Dim db As DAO.Database
Dim newPath As DAO.Recordset
Dim myDept As DAO.Recordset
Dim newDept As String
Dim myCMD As DAO.Recordset
Dim newCMD As String
Dim myDep As DAO.Recordset
Dim newDep As String

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 myDept = db.OpenRecordset("qryDepartmentCodes")
Set myCMD = db.OpenRecordset("qryCommandCodes")
Set myDep = db.OpenRecordset("qryDeputyCodes")

Const xlCenter As Long = -4108

strPath = newPath!Out_Path & "CombinedTimecards_Crosstab.xls"

Set ApXL = CreateObject("Excel.Application")
Set xlWSh = ApXL.Workbooks.Open(strPath).Sheets(1)
ApXL.Visible = True
    
    With ApXL
            .Application.Sheets("Compliance_Summary").Select
            .Application.Cells.Select
            .Application.Selection.ClearFormats
            .Application.Selection.Font.Name = "Arial"
            .Application.Selection.Font.Size = 10
            .Application.Range("A1:E1").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:E").Select
            .Application.Selection.Columns.AutoFit
            .Application.Range("B:B").Select
            .Application.Selection.Style = "Percent"
            .Application.Selection.NumberFormat = "0.0%"
            .Application.Range("C:E").Select
            .Application.Selection.Style = "Comma"
            .Application.Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
            .Application.Range("A1").Select
            
            .Application.Selection.EntireRow.Insert
            .Application.Selection.EntireRow.Insert
            .Application.Selection.EntireRow.Insert
            .Application.Selection.EntireRow.Insert
            .Application.Range("A1:E1").Select
            .Application.Selection.HorizontalAlignment = xlCenter
            .Application.Selection.Merge
            .Application.Selection.Font.Bold = True
            .Application.Selection.Font.Size = 14
            .Application.ActiveCell.FormulaR1C1 = "Compliance Summary"
            .Application.Range("A2:E2").Select
            .Application.Selection.HorizontalAlignment = xlCenter
            .Application.Selection.Merge
            .Application.Selection.Font.Bold = True
            .Application.Selection.Font.Size = 14
            .Application.ActiveCell.FormulaR1C1 = "As of:"
            .Application.Range("A3:E3").Select
            .Application.Selection.HorizontalAlignment = xlCenter
            .Application.Selection.Merge
            .Application.Selection.Font.Bold = True
            .Application.Selection.Font.Size = 14
            .Application.ActiveCell.FormulaR1C1 = "=NOW()"
            .Application.Selection.NumberFormat = "[$-409]mmmm d, yyyy;@"
            

            
            
            .Application.Sheets("All_Delinquent").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:M1").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:M").Select
            .Application.Selection.Columns.AutoFit
            
            .Application.Range("A1").Select
            
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.Selection.EntireRow.Insert
            .Application.Selection.EntireRow.Insert
            .Application.Range("A1:L1").Select
            .Application.Selection.HorizontalAlignment = xlCenter
            .Application.Selection.Merge
            .Application.Selection.Font.Bold = True
            .Application.Selection.Font.Size = 14
            .Application.ActiveCell.FormulaR1C1 = newCMD
                        
            .Application.Range("A:L").Select
            .Application.Selection.Columns.AutoFit
            
            .Application.Range("A2").Select
            .Application.ActiveCell.FormulaR1C1 = "=DCOUNTA(R[1]C:R[11073]C,R[1]C,R[1]C:R[11073]C)"
            .Application.Range("B2").Select
            .Application.ActiveCell.FormulaR1C1 = "Delinquent Timecards"
            .Application.Range("A2:B2").Select
            .Application.Selection.Font.Bold = True
            .Application.Selection.Font.Color = -16776961
            .Application.Selection.Font.Size = 12
            
            .Application.Range("A1").Select

myCMD.MoveNext

Loop

Do Until myDep.EOF

newDep = myDep!Dep_CDR

            .Application.Sheets(newDep).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.Selection.EntireRow.Insert
            .Application.Selection.EntireRow.Insert
            .Application.Range("A1:L1").Select
            .Application.Selection.HorizontalAlignment = xlCenter
            .Application.Selection.Merge
            .Application.Selection.Font.Bold = True
            .Application.Selection.Font.Size = 14
            .Application.ActiveCell.FormulaR1C1 = newDep
            .Application.Range("A:L").Select
            .Application.Selection.Columns.AutoFit
            
            .Application.Range("A2").Select
            .Application.ActiveCell.FormulaR1C1 = "=DCOUNTA(R[1]C:R[11073]C,R[1]C,R[1]C:R[11073]C)"
            .Application.Range("B2").Select
            .Application.ActiveCell.FormulaR1C1 = "Delinquent Timecards"
            .Application.Range("A2:B2").Select
            .Application.Selection.Font.Bold = True
            .Application.Selection.Font.Color = -16776961
            .Application.Selection.Font.Size = 12
            
            .Application.Range("A1").Select

myDep.MoveNext

Loop

Do Until myDept.EOF

newDept = myDept!Dept

            .Application.Sheets(newDept).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.Selection.EntireRow.Insert
            .Application.Selection.EntireRow.Insert
            .Application.Range("A1:L1").Select
            .Application.Selection.HorizontalAlignment = xlCenter
            .Application.Selection.Merge
            .Application.Selection.Font.Bold = True
            .Application.Selection.Font.Size = 14
            .Application.ActiveCell.FormulaR1C1 = newDept
            .Application.Range("A:L").Select
            .Application.Selection.Columns.AutoFit
            
            .Application.Range("A2").Select
            .Application.ActiveCell.FormulaR1C1 = "=DCOUNTA(R[1]C:R[11073]C,R[1]C,R[1]C:R[11073]C)"
            .Application.Range("B2").Select
            .Application.ActiveCell.FormulaR1C1 = "Delinquent Timecards"
            .Application.Range("A2:B2").Select
            .Application.Selection.Font.Bold = True
            .Application.Selection.Font.Color = -16776961
            .Application.Selection.Font.Size = 12
            
            .Application.Range("A1").Select

myDept.MoveNext

Loop

            .Application.Sheets("Compliance_Summary").Select
  
             

             .Application.ActiveWorkbook.Save
             .Application.ActiveWorkbook.Close
             .Quit

          
    End With

Set ApXL = Nothing
Set xlWSh = Nothing
    

End Function
 
Quick guess might just be the color 16628595 you selected. Play with manually making the formatting changes [while recording your steps as a macro] to figure out what is not compatible with excel 2003 when you save the file as an .xls.
 
Actually, I think I know the problem.

You are formatting every cell on the sheets, which is not good (as it makes the file size larger -actually much larger). Now, if you do that in Excel 2007 then you have 1 million rows and 65,000 columns (or something like that) and then you try to save to an Excel 2003 format which only has 65,535 rows (approx) and 255 columns, then you can't do it because the data won't fit.

ONLY format the areas which actually have data.
 
ghudson said:
Quick guess might just be the color 16628595 you selected. Play with manually making the formatting changes [while recording your steps as a macro] to figure out what is not compatible with excel 2003 when you save the file as an .xls.
Click to expand...

Well, I had to set this asside for a few days but after testing the steps in formatting, it turns out that is is the background color of the header rows that is causing the error. With that said, does anyone know the correct color coding for Excel 2003?

And by the way Bob... I'll look into how many rows/columns I am formatting and make sure I am only getting the ones I need... Thanks!
 
Okay Team! Turns out this was way esaier than I thought! All I had to do was replace all occurances of

Code: 
.Application.Selection.Interior.Color = 16628595

With

Code: 
.Application.Selection.Interior.Color = RGB(0, 204, 255)

I think the color is slightly different than the original, but not really an issue!
 
You must log in or register to reply here.

Similar threads

S
REPORT TO EXCEL FORMAT
Replies
8
Views
1,110
Gasman
Gasman
C
Solved problem opening a .csv file in excel - dates all messed up
2
Replies
23
Views
3,178
CJ_London
C
J
Use VBA to export form data to Excel, but keep getting "We Found a Problem..." when opening the file in Excel
Replies
5
Views
1,156
June7
June7
D
Solved Date is displaying odd behavior
Replies
14
Views
846
Ken Sheridan
Ken Sheridan
A
Automation Error
Replies
10
Views
703
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

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