I'm working on an Access application that generates Excel Spreadsheets for the client. I've setup a table to store spreadsheet layout parameters, i.e. header/footer text strings, PaperSize, Margins, etc. The PageSetup.LeftMargin (.RightMargin, etc.) in Excel is of data type Double. I set my table field data types to be double. When I attempt to pass the values to Excel by using a reocrdset I get an error
Set rsSpecs = db.OpenRecordset("tblOutputFileSpecs", dbOpenDynaset)
Set xlSheet = ExcelApp.ActiveWorkbook.Sheets(1)
.PageSetup.LeftMargin = rsSpecs!LeftMargin
If I add a variable declared as type Double I can get it to work
Dim dblMargin As Double
dblMargin = rsSpecs!LeftMargin
.PageSetup.LeftMargin = dblMargin
Why? I've run into the same issue with .PageSetup.Zoom - even though acceptable values are between 10 & 400 Excel lists the data type as variant. I have to assign the value from the table to a variable of type Variant to make it work.
I know I've got it working but I am curious as to why it isn't working the other way.