Can I export table data to Excel In a set Format?

[chewy]

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!

 

[mrabrams]

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]

how do you create this macro? Or any other ideas?

 

[mission2java_78]

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

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]

that code is beyond me. How would I get the rptMain into the excell file without ruing the formatting?

 

[mission2java_78]

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]

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

 

[mission2java_78]

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]

They are the fields. they may be named a little differently. But im not too attached to the names. What ever is easier.

 

[mission2java_78]

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

 

[mission2java_78]

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]

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!

 

[mission2java_78]

Post back monday...ill try to swing by.

Jon

 

[mission2java_78]

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]

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?

 

[mission2java_78]

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

Check off excel object model 9.0 / 8.0

Jon

 

[chewy]

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]

Actually...I commented out the errror handling and now it pinpoints the error line to
Set rs = db.OpenRecordset(strSQL)

 

[mission2java_78]

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]

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?