By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,121 Members | 1,782 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,121 IT Pros & Developers. It's quick & easy.

Problem creating Excel files from an Access Module

P: n/a
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?

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
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
Nothing really matters except Boats, Sex, and Rock'n'Roll
OK, OK; computer programming for scientific research also matters
================================================== ====================
Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.