If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > create an excel spreadsheet using vba code in access

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: London,UK
Posts: 54
create an excel spreadsheet using vba code in access

can this be done?

I have made a database and a front end to go with it. I want the user to be able to export the data to excel. i have currently only allowed to export in csv but would like to do the same in excel.

Any guidence much appreciated.

jas
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Oct 2003
Posts: 311
Re: create an excel spreadsheet using vba code in access

Quote:
Originally posted by tinkxtheminx
can this be done?

I have made a database and a front end to go with it. I want the user to be able to export the data to excel. i have currently only allowed to export in csv but would like to do the same in excel.

Any guidence much appreciated.

jas
This will get u started,, if u need more lemeno

Sub main()
Dim sql As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset


sql = "SELECT Deal.Deal, Deal.Valid, Deal.DropDown FROM Deal WHERE (((Deal.Valid)=""V"") AND ((Deal.DropDown)=""R"")) OR (((Deal.Valid)=""V"") AND ((Deal.DropDown)=""C""));"

rs.Open sql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdText

XL rs

Set rs = Nothing




End Sub
Sub XL(rs As ADODB.Recordset)
Dim XL As Excel.Application
Dim Book As Excel.Workbook
Dim wks As Excel.Worksheet

Set XL = New Excel.Application
XL.Visible = True
Workbooks.Open fileName:= _
"c:\CurrentDealList.xls"

Sheets("DealList").Activate
[a1].Select
SelectAll
Selection.Delete
Range("A1").CopyFromRecordset rs
Set XL = Nothing
' garbage XL
End Sub
Sub SelectAll()
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select

End Sub


Note i dont dipose of exl app or something like that so there can be memory leak or something like that,,, i think so if lots of ppl are going to be doin this u need to keep that in mind - im the only person that uses this code so its good enuf 4 me

M~
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On