Creating an Excel Sheet from an Access Ap.

  • Thread starter Thread starter Salvador M
  • Start date Start date
S

Salvador M

Hi, I'm trying to create an Excel Sheet from an Access
Aplication. I already have the info in my prog. now I would
like to write the column header plus the columns data.
Is there any way way to create an Excel file, access an
especific cell write the data of that cell and after
finishing close the file?

Your help will be appreciated

Salvador
 
Here's some sample code for opening an EXCEL workbook:

Public Sub TestMacroRun()
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Set xlx = CreateObject("excel.application")
Set xlw = xlx.workbooks.Open("C:\Filename.xls")
Set xls = xlw.Worksheets("WorksheetName")
Set xlc = xls.Range("A1")
' put code here to write into the cells etc.
' .
' .
' .
Set xlc = Nothing
Set xls = Nothing
xlw.Save
xlw.Close False
Set xlw = Nothing
xlx.Quit
Set xlx = Nothing
End Sub
 
Thanks Ken for your Response, but....
1) How do you write to a cell ?
2) Does the .range property makes you access an especific
cell ?
3) How can I look for other properties of the worksheet,
because when I'm writing to the access editor and put
the period I don't get a Member list of that object
I have already activated the Microsoft Excel 9.0 Object
Library.

Looking forward for you answer

Salvador
 
Sorry. I was assuming that you were familiar with VBA code for EXCEL.

To write to a specific cell, you use the .Range property of a Worksheet. For
example, in the code I had given you, if you wanted to write the value 12 to
cell A1 on the xls worksheet:
xls.Range("A1").Value = 12

You can get the properties and methods of a worksheet if you open EXCEL and
then open Visual Basic Editor, then open the Help file. That gives you info
about EXCEL's objects, their properties and methods, etc. If you're very
unfamiliar with EXCEL VBA code, I recommend that you begin in EXCEL and then
copy the code from there into ACCESS module; it'll get you closer to where
you want to be.

Alternatively, if you just want to see the properties and methods, you can
use the Object Browser in VBE in ACCESS to see what they are. That won't
tell you what they do, though.
 
An EXCEL 97 and higher spreadsheet can have up to 256 columns and 65536
rows.

You should not have any problem writing into row 356.

You'll need to provide more information about the code that you're using and
the setup of the spreadsheet before I could venture a suggestion about this
problem.
 
Back
Top