Everything works fine in Access, but when I double-click on the
resultant Excel files the first one opens correctly, but subsequent
ones, and any other Excel files I try to open, fail to display at all;
I get the Excel window frame with corrupted contents. The only way I
can get decent functionality from Excel is to open Excel from the
Start menu, open each of these new files from the Excel File menu, and
save each one. Thereafter Excel functions normally, except that,
probably the first time I opened one of these files, my Excel
preferences were all changed (probably reverted to default?), and
Excel no longer looks the way I am accustomed to seeing it.
I cannot send these files out to the recipient sites if they will
overwrite the Excel user preferences at each site. I could
conceivably open and save each one before sending it, but that takes a
large bite out of the purpose of automating this process.
Any ideas about what I need to do differently to save these files as
well-behaved Excel files?
Thanks,
-Richard
pseudoCallingRoutine
initialize site_hold to -1 (all site IDs are posints)
.-if user selects folder for output
| .-if ordered ADODB.Recordset successfully opened & > 0 records
| | .-for each record
| | | .-if RecSet.SiteID <> site_hold
| | | | if site_hold <> -1 then uSaveXLS
| | | | if not uNewXLS then exit with an error message
| | | | save RecSet.SiteID in site_hold
| | | | generate filespec as C:\folder\sitename.xls
| | | | format some cells
| | | | (re)initialize row counter
| | | `---end if
| | | populate row with values from this record
| | | increment row counter
| | `---next record
| | uSaveXLS 'last one
| `---end if
`---end if
end procedure
Public Function uNewXLS(xl As Object, ws As Worksheet) As Boolean
uNewXLS = False
On Error GoTo ErrExit
Set xl = CreateObject("Excel.Sheet")
Set ws = xl.Worksheets(1)
uNewXLS = True
ErrExit:
End Function
Public Sub uSaveXLS(filespec As String, xl As Object, ws As Worksheet)
ws.SaveAs filespec
Set ws = Nothing
Set xl = Nothing
End Sub
--
================================================== ====================
Richard Hopley Winston-Salem, NC, USA
rhopley[at]earthlink[dot]net
Nothing really matters except Boats, Sex, and Rock'n'Roll
rhopley[at]wfubmc[dot]edu
OK, OK; computer programming for scientific research also matters
================================================== ====================