Can I export table data to Excel In a set Format? (1 Viewer)

chewy

SuperNintendo Chalmers
Local time
Today, 14:34
Joined
Mar 8, 2002
Messages
581
I want to export a table to Excel. But I already have a spreadsheet with a format that I want. When I export the table to a file already created in the format it is in now. How would I do this? I can post the examples if necessary. I can also explain more if you need it. Thanks!
 
M

mrabrams

Guest
There's (at least) 2 ways to do this. One I can help you with, one I can tell you about, and someone will have to continue!

The simple (crude?) way :
I created a Macro in Excel with my formatting.
Whenever I export from Access to Excel, I open the spreadsheet, run the macro (1 click) and it's done.

The sophisticated way:
Use Access to Excel Automation. (I can summarize it, but not code it!!)
Through Access, open an instance of Excel.
Code the format.
Code the export.
etc......

Hope this is a start.

Michael
 

chewy

SuperNintendo Chalmers
Local time
Today, 14:34
Joined
Mar 8, 2002
Messages
581
how do you create this macro? Or any other ideas?
 
M

mission2java_78

Guest
Export a table to excel?

Just highlight the table->file -> export...choose the excel file type 2k or 97 etc.

Otherwise you can also code it which is simple

Code:
Dim objXL As Excel.Application
Dim objXLWrkBk As Excel.Workbook
Dim objXLWrkSht As Excel.Worksheet
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM tblProposals WHERE ProposalID= " & lngProposalID)

If rs.BOF Then
    'record not found?
Else
    Set objXL = CreateObject("Excel.Application")
    Set objXLWrkBk = objXL.Workbooks.Open(strCalcFilePath)
    Set objXLWrkSht = objXLWrkBk.Worksheets("Original")
    objXL.DisplayAlerts = False
    objXL.Visible = False

    objXLWrkSht.Range("A1").Activate

    'first store the quote number
    objXLWrkSht.Range("D1").Value = Nz(rs("QuoteNumber").Value)

    'project description & rfq
    objXLWrkSht.Range("J1").Value = Nz(rs("Description").Value) & "-" & Nz(rs("CustomerRFQ").Value)

    'set year of delivery
    objXLWrkSht.Range("Q1").Value = Nz(rs("DeliveryDate").Value)

    'set originator
    objXLWrkSht.Range("X1").Value = Nz(rs("Originator").Value)

    'set customer
    objXLWrkSht.Range("J2").Value = Nz(DLookup("CustomerName", "tblCustomers", "CustomerID= " & rs("CustomerID").Value)) & "-" & Nz(DLookup("Sites", "tblSites", "SiteID= " & rs("SiteID").Value))
    
    'set commission
    objXLWrkSht.Range("D3").Value = Nz(DLookup("Commission", "tblCommissions", "CommissionID= " & rs("CommissionID").Value))
    
    'set country
    objXLWrkSht.Range("Q2").Value = Nz(DLookup("CountryName", "tblCountries", "CountryID= " & rs("CountryID").Value))

    'set quote due date...
    objXLWrkSht.Range("X2").Value = Nz(rs("QuoteDueDate").Value)
    
    objXLWrkBk.Close SaveChanges:=True
    'objXLWrkBk.Save
    'objXLWrkBk.Close
    objXL.Quit
End If

Done:
Set rs = Nothing
Set db = Nothing
Set objXL = Nothing
Set objXLWrkBk = Nothing
Set objXLWrkSht = Nothing
Exit Function

Err_Handler:
MsgBox Err.Description, vbCritical, "Error #: " & Err.Number
Resume Done
Jon
 

chewy

SuperNintendo Chalmers
Local time
Today, 14:34
Joined
Mar 8, 2002
Messages
581
that code is beyond me. How would I get the rptMain into the excell file without ruing the formatting?
 

Attachments

  • ferris.zip
    84.2 KB · Views: 269
M

mission2java_78

Guest
Give me more info...is that excel file already generated with row four rows set as a freeze pane and all the headings already there? Or do oyu create the excel file entirely?

Jon
 

chewy

SuperNintendo Chalmers
Local time
Today, 14:34
Joined
Mar 8, 2002
Messages
581
the file is already created just as you see it, except all the customer info is wiped out daily. I am currently working on a database that will store this long term. But I just want Access to open the file and spit the data into the Excel spreadsheet and put them in the correct rows.

Thanks
 
M

mission2java_78

Guest
Do you have those columns of excel as fields in your db? If so this is simple we just tear up my code for your purpose.

Jon
 

chewy

SuperNintendo Chalmers
Local time
Today, 14:34
Joined
Mar 8, 2002
Messages
581
They are the fields. they may be named a little differently. But im not too attached to the names. What ever is easier.
 
M

mission2java_78

Guest
Ok...lets get down and dirty...wait that didnt sound good.

Here goes
On Error Goto Err_Handler

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim strSQL As String
Dim objXL As Excel.Application
Dim objXLWrkBk As Excel.Workbook
Dim objXLWrkSht As Excel.Worksheet
Dim lngStartRange as Long

strSQL = "SELECT * FROM YourTableNameGoesHERE"

Set db=currentdb
set rs=db.openrecordset(strSQL)

if rs.bof then
'no records
'so what to do...prolly nothing?
'or you could put a messagebox here
else
lngStartRange = 5 'start at row 5
Set objXL = CreateObject("Excel.Application")
Set objXLWrkBk = objXL.Workbooks.Open("PATH TO THAT EXCEL FILE")
Set objXLWrkSht = objXLWrkBk.Worksheets("Sheet1") 'takes the name of the tab in Excel in your case sheet1
objXLWrkSht.Range("A1").Activate
while not rs.eof
objXLWrkSht.Range("A" & lngStartRange).Value = Nz(rs("Customer").Value)
objXLWrkSht.Range("B" & lngStartRange).Value = Nz(rs("Customernumb").Value)
objXLWrkSht.Range("C" & lngStartRange).Value = Nz(rs("CustomerCheck").Value)
objXLWrkSht.Range("D" & lngStartRange).Value = Nz(rs("CheckAmount").Value)
objXLWrkSht.Range("E" & lngStartRange).Value = Nz(rs("InvNumber").Value)
objXLWrkSht.Range("F" & lngStartRange).Value = Nz(rs("InvoiceAmountPaid").Value)
objXLWrkSht.Range("G" & lngStartRange).Value = Nz(rs("Discount").Value)
objXLWrkSht.Range("H" & lngStartRange).Value = Nz(rs("Gross").Value)
lngStartRange=lngStartRange + 1 'increment the row for next customer
rs.MoveNext 'move to next customer
wend
objXLWrkBk.Close SaveChanges:=True
end if

Done:
Set rs = Nothing
Set db = Nothing
Set objXL = Nothing
Set objXLWrkBk = Nothing
Set objXLWrkSht = Nothing
Exit Sub

Err_Handler:
MsgBox Err.Description, vbCritical, "Error #: " & Err.Number
Resume Done


Also the best best thing to do before modifying the original file is to FIRST copy that file somewhere else..because you might run this code at different days..you dont want to lose the format of the excel file you want it to be an empty sheet each time you run this code. So you SHOULD and the BEST way to do this is to copy that file (it prolly sits on the network right?). Then all you do is
store where you saved it in a string and then make sure you call it in this line:
Set objXLWrkBk = objXL.Workbooks.Open("PATH TO THAT EXCEL FILE")

Jon
 
M

mission2java_78

Guest
Use FileCopy to copy that file than store it in the string to reference it later on.

For instance,

FileCopy("c:\temp\blah", "c:\temp3\blah")

Jon
 

chewy

SuperNintendo Chalmers
Local time
Today, 14:34
Joined
Mar 8, 2002
Messages
581
Thanks sooo mcuh. I dont think I will be able to try it out today. Just got a bunch of work to do and have to leave in a half hour.

Thanks Again!
 
M

mission2java_78

Guest
I forgot one thing...after the save piece of code...objXLWrkBk.Close savechanges yada yada

do this:

objXL.Quit

That way excel quits :). Otherwise you'll have users calling you about casper looking excel files that are killing their OS processes.

Just a thought :) Have a good weekend.


Jon
 

chewy

SuperNintendo Chalmers
Local time
Today, 14:34
Joined
Mar 8, 2002
Messages
581
I tried it and I got a User Defined Type not Defined error on
Dim objXL As Excel.Application

Where should I put this code?
 
M

mission2java_78

Guest
You need to include the reference to excel in the tools options.

Check off excel object model 9.0 / 8.0

Jon
 

chewy

SuperNintendo Chalmers
Local time
Today, 14:34
Joined
Mar 8, 2002
Messages
581
I put it all in a command button. I click and get the error 3061 "Too few parameteres. Expected 2" It doesnt open the debugger so I dont know where the error is. I added the reference to Excel 8.0

Can this code go anywhere because I tried putting it in a module and got outside call.

You there java?
 

chewy

SuperNintendo Chalmers
Local time
Today, 14:34
Joined
Mar 8, 2002
Messages
581
Actually...I commented out the errror handling and now it pinpoints the error line to
Set rs = db.OpenRecordset(strSQL)
 
M

mission2java_78

Guest
Thats right...the reason being is you are using a query right?

If so you need a second argument in there.
I can't remember the argument that it is but if you're not using DYNAMIC SQL or a table name you will need that second argument. In the openrecordset method.

Jon
 

chewy

SuperNintendo Chalmers
Local time
Today, 14:34
Joined
Mar 8, 2002
Messages
581
how can such a seemingly easy thing turn into such a pain?

I have read a few articles on this and some have used
Set rs = db.QueryDefs("qryMain")

Would this help me or is this not what im looking for?
 

Users who are viewing this thread

Top Bottom