Access and excel (1 Viewer)

associates · Nov 22, 2006

Hi,

I was wondering if anyone might be able to help me here.

I have a question. Is it possible to create new worksheet in Access using VBA code?

What happen is that i have a form in Access that would export data from Access to Excel. I know that i could use the built-in feature provided in Access to export to excel. But because i guess i want to be more flexible in managing the data on excel spreadsheet. So would it be possible to write the excel programming in the Access?

Sorry for asking but i just want to know.

Thank you in advance

Bat17 · Nov 22, 2006

Forums are made for asking questions

have a look at this code and see if gets you pointed in the right direction.
You will need to set a reference to Excel, in any module, Tools>References scroll down and make sure that "Microsoft Excel xxx Object Libary" (xxx is the version number which can vary)

Code:

Sub ExportTo()
Dim appXL As excel.Application
Dim wk As excel.Workbook
Dim strSQL As String
Dim rs As DAO.Recordset

strSQL = "Select * From tblMyData"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenForwardOnly)
'rs.MoveFirst

Set appXL = New excel.Application
Set wk = appXL.Workbooks.Add("C:\tblMyData1.xlt")
wk.Worksheets(1).Range("A1").CopyFromRecordset rs

'appXL.Visible = True
wk.SaveAs "C:\MyData" & Format(Now, "yyymmddhhnn") & ".xls"
wk.Close
appXL.Quit


Set rs = Nothing
Set wk = Nothing
Set appXL = Nothing

End Sub

HTH

Peter

associates · Nov 22, 2006

Thank you Peter for your reply.

I tried your code but unfortunately, my Access 2003 didn't recognise the following line of code.

Dim appXL As excel.Application
Dim wk As excel.workbook

I got error that says "user defined type not defined"

Do i need to import any libraries?

Also, i noticed that your code
wk.Worksheets(1).Range("A1").CopyFromRecordset rs

does it mean if i want to create another worksheets, can i just do the following

wk.Worksheets(2).range("A1").CopyFromRecordset rs

what about if i want to name the worksheet to say "Data" for the first one and "Result" for the second worksheet. Is this possible?

Thank you in advance

Bat17 · Nov 23, 2006

you should not need to import any libaries just set the reference to Excel as explained above.

This code example lets you delete a named sheet, create and place a new one at the end of the existing sheets and to rename it.

Code:

Set xlApp = New excel.Application
Set wk = xlApp.Workbooks.Open("C:\MyFile.xls")
xlApp.DisplayAlerts = False
    wk.Sheets("Sheet2").Delete
xlApp.DisplayAlerts = True
xlApp.Worksheets.Add.Move After:=xlApp.Worksheets(xlApp.Worksheets.Count)
xlApp.Sheets(xlApp.Worksheets.Count).Name = "newSheet"

wk.Worksheets(1).Range("A1").CopyFromRecordset rs

pastes a set of records from 'rs' in to the first worksheet 'wk.Worksheets(1)'

HTH

Peter

Access and excel (1 Viewer)

associates

Registered User.

Bat17

Registered User.

associates

Registered User.

Bat17

Registered User.

Similar threads

Users who are viewing this thread