1

I've written an Access 2007 application that opens an empty Excel spreadsheet and writes into it with the following (shortened) VBA code:

Dim Excel_App As Excel.Application  
Set Excel_App = CreateObject("Excel.Application")  
Excel_App.Visible = True  
Excel_App.Workbooks.Add  
With Excel_App  
  .Columns("A:ZZ").ColumnWidth = 25  
  .Range("A2:ZZ2").VerticalAlignment = xlCenter  
  .Range("A2:ZZ2").Font.FontStyle = "Bold"  
  .Range("A" & CStr(iHeadingRows)).Select  
  .ActiveCell.FormulaR1C1 = "ABC"  
End With

The above code works on all installations of Windows (Vista|Xp)/Access 2007 (updated with all patches) I've tested but the customer's one. On his one, the empty Excel spreadsheet gets opened but remains empty when I try to write into it and a runtime error is generated.

Is it possible some system policy for the user or some particular setup is blocking the normal behaviour I've tested on the other PCs? And if that's the case, what can be done to fix it in the most unobtrusive way?

Thanks for any advice!

David-W-Fenton
  • 22,871
  • 4
  • 45
  • 58
Andrea
  • 13
  • 1
  • 3
  • 1
    might want to list the runtime error and the location in the code that the error is thrown. – Patrick Jan 10 '11 at 13:17
  • I know, but unfortunately I can't replicate the error on my PCs and customer is kinda hard to reach :-( I'll try to get him and update this... – Andrea Jan 10 '11 at 13:18
  • 1
    Does your customer have Excel 2007? Excel 2003 has only 256 Columns that means the Column "ZZ" can't be used. – marg Jan 10 '11 at 13:27
  • He should have Excel 2007. Still this is a good point, I remember seeing that running in "compatibility mode" (written on the title of the window), would that mean it will inherit the above limitation on the number of columns? I'll check this out, thank you! – Andrea Jan 10 '11 at 14:28
  • From what I'm reading about "compatibility mode", it could indeed be that the reason! I'll try to send an updated version to my customer to test it asap, thanks again! – Andrea Jan 10 '11 at 14:44

1 Answers1

0

Just so this Question doesn't remain without an answer. Apparently the source of the problem was, that prior Excel Versions only had 256 columns and you tried to access a cell in the column "ZZ".

Here is a solution that should run with all Excel Versions:

Dim Excel_App As Excel.Application
Set Excel_App = CreateObject("Excel.Application")
Excel_App.Visible = True
Excel_App.Workbooks.Add
With Excel_App
.Range("A:A").EntireRow.ColumnWidth = 25
.Range("A2").EntireRow.VerticalAlignment = xlCenter
.Range("A2").EntireRow.Font.FontStyle = "Bold"
.Range("A" & CStr(iHeadingRows)).Select
.ActiveCell.FormulaR1C1 = "ABC"
End With
marg
  • 2,817
  • 1
  • 31
  • 34
  • 1
    Solution confirmed. I've managed to reproduce the error on my PC by forcing Excel to stay in "compatibility mode". Changing the column index from "ZZ" to a lower value compatible with older Excel solved the problem. Thanks a lot! – Andrea Jan 13 '11 at 14:30