Write access recordset to Excel file

B

bulrush

Registered User.
Local time
Today, 18:21
Joined
Sep 1, 2009
Messages
209
I am currently writing tab-separated text files, which are then imported manually into an Excel tab. I would like to use Access 2003 to write directly to an Excel .XLS file with one tab.

I've seen some examples on this site but there's a lot of extra code in those samples and I don't know what is required and what is "extra" stuff I don't need. Could someone write some basic code for me showing me how to create objects, assign data to cells, and close the objects?

The .XLS file will be in the same directory as the MDB file. The user will determine the filename. My spreadsheets have multiple columns and up to hundreds of rows. But each row has the same format as the next.

Thank you.

p.s. I think my Excel version is older than my Access 2003. I only upgrade Access since Excel works just fine as it is.
 
check out the transferspreadsheet command in the help
 
Thanks. That's an ultra simple way to do it and will not meet my needs. As I loop through the recordset I need to make many calculations, constructing fields using various pieces of data.
 
Easy to output a query or table using the TransferSpreadsheet method. Append data to a table if you are using numerous queries.

Code: 
DoCmd.TransferSpreadsheet acImport, , "YourTableOrQuery", "X:\YourOutputFile.xls", True

p.s. I think my Excel version is older than my Access 2003. I only upgrade Access since Excel works just fine as it is.
Click to expand...
That makes no sense to not upgrade all of your Office programs to the same version at the same time.
 
bulrush said:
Thanks. That's an ultra simple way to do it and will not meet my needs. As I loop through the recordset I need to make many calculations, constructing fields using various pieces of data.
Click to expand...

It's actually not that hard to use this method to construct complex results. I use loops and build-on-the-fly SQL to populate Excel from Access. The code below works for me. It is based on the advice and assistance of the very fine community of programmers here on this forum! Maybe you can adapt it for your use...

Code: 
Public Function CopyToWorkbook()

'  This is the Baseline Function with unformatted data

Dim db As DAO.Database
Dim newPath As DAO.Recordset
Dim myDept As DAO.Recordset
Dim newDept As String
Dim myCMD As DAO.Recordset
Dim newCMD As String
Dim myDep As DAO.Recordset
Dim newDep As String
Dim strPath As String
Dim SQL As String
Set db = CurrentDb()
Set newPath = db.OpenRecordset("Set_Path")
Set myDept = db.OpenRecordset("qryDepartmentCodes")
Set myCMD = db.OpenRecordset("qryCommandCodes")
Set myDep = db.OpenRecordset("qryDeputyCodes")

strPath = newPath!Out_Path & "CombinedTimecards_Crosstab.xlsx"

' LoadFileName

     DoCmd.TransferSpreadsheet acExport, 8, "qryFinalCompSum", strPath, True, "Compliance Summary"
     DoCmd.TransferSpreadsheet acExport, 8, "qryDelinquentList", strPath, True, "Delinquent_List"
         
     
DoCmd.SetWarnings False
     
' Start Export of Command Code Tabs

Do Until myCMD.EOF

newCMD = myCMD!CMD_Code

DoCmd.OpenQuery "xDeleteCommandDelinquentList"
    
  SQL = "INSERT INTO tempCommandDelinquentList " & _
        "SELECT qryDelinquentList.[Dept], qryDelinquentList.[Title Rank], " & _
        "qryDelinquentList.[Name], qryDelinquentList.[SkillType], " & _
        "qryDelinquentList.[People Group], qryDelinquentList.[Time Approver], " & _
        "qryDelinquentList.[Person Types], qryDelinquentList.[Status], " & _
        "qryDelinquentList.[Reason], qryDelinquentList.[Timecard Start Date], " & _
        "qryDelinquentList.[Timecard Stop Date] " & _
        "FROM qryDelinquentList " & _
        "WHERE qryDelinquentList.[CMD_Code] ='" & newCMD & "';"

 DoCmd.RunSQL SQL
     
     DoCmd.TransferSpreadsheet acExport, 8, "tempCommandDelinquentList", strPath, True, newCMD
     
myCMD.MoveNext

Loop

DoCmd.OpenQuery "xDeleteCommandDelinquentList"
     
' Start Export of Deputy Code Tabs

Do Until myDep.EOF

newDep = myDep!Dep_CDR

DoCmd.OpenQuery "xDeleteDeputyDelinquentList"
    
  SQL = "INSERT INTO tempDeputyDelinquentList " & _
        "SELECT qryDelinquentList.[Dept], qryDelinquentList.[Title Rank], " & _
        "qryDelinquentList.[Name], qryDelinquentList.[SkillType], " & _
        "qryDelinquentList.[People Group], qryDelinquentList.[Time Approver], " & _
        "qryDelinquentList.[Person Types], qryDelinquentList.[Status], " & _
        "qryDelinquentList.[Reason], qryDelinquentList.[Timecard Start Date], " & _
        "qryDelinquentList.[Timecard Stop Date] " & _
        "FROM qryDelinquentList " & _
        "WHERE qryDelinquentList.[Dep_CDR] ='" & newDep & "';"

 DoCmd.RunSQL SQL
     
     DoCmd.TransferSpreadsheet acExport, 8, "tempDeputyDelinquentList", strPath, True, newDep
     
myDep.MoveNext

Loop

DoCmd.OpenQuery "xDeleteDeputyDelinquentList"
     
'  Start Export of Department Code Tabs
     
Do Until myDept.EOF

newDept = myDept!Dept

DoCmd.OpenQuery "xDeletetempDeptDelinquentList"
    
  SQL = "INSERT INTO tempDeptDelinquentList " & _
        "SELECT qryDelinquentList.[Dept], qryDelinquentList.[Title Rank], " & _
        "qryDelinquentList.[Name], qryDelinquentList.[SkillType], " & _
        "qryDelinquentList.[People Group], qryDelinquentList.[Time Approver], " & _
        "qryDelinquentList.[Person Types], qryDelinquentList.[Status], " & _
        "qryDelinquentList.[Reason], qryDelinquentList.[Timecard Start Date], " & _
        "qryDelinquentList.[Timecard Stop Date] " & _
        "FROM qryDelinquentList " & _
        "WHERE qryDelinquentList.[Dept] ='" & newDept & "';"

 DoCmd.RunSQL SQL
     
     DoCmd.TransferSpreadsheet acExport, 8, "tempDeptDelinquentList", strPath, True, newDept
  
     
myDept.MoveNext

Loop

DoCmd.OpenQuery "xDeletetempDeptDelinquentList"

DoCmd.SetWarnings True

DoCmd.TransferSpreadsheet acExport, 8, "CombinedTimecards_Crosstab", strPath, True, "CombinedTimecards_Crosstab"

End Function
 
BamaColtsFan: I'm not that familiar with SQL. Doesn't your SQL INSERT statement give you a single spreadsheet with only the last record inserted?

I'm still looking for a solution so, while I loop through my recordset, I can do my calculations and write to individual columns in a given row. I simply can't do it with TransferSpreadsheet and a query.

Here is my existing code.
Code: 
SEP=chr(9)
Do While Not (Myset.EOF = True)
        signtype = "C"
        signsize = "11x7"
        ' Construct ItemID from PLU.
        plu = ""
        If (Len(Myset!Myplu) = 4) Then
            plu = "PLU"
        End If
        If (Len(Myset!Myplu) = 0) Then ' Get auto id from Control table.
            autol = ControlReadLong("MID")
            Call ControlSaveNum("MID", autol + 1)
            plu = Format(autol, "000000") ' Use leading zeros.
        End If
        plu = plu & Myset!Myplu & signtype & "-" & signsize
        s = plu & SEP
        
        s = s & Myset!ProductClass & SEP
        
        t = Trim(Myset!NameOfProduce1) & " " & signsize & " " & Trim(Myset!FoodCat) & " Produce Sign" ' Description
        s = s & t & SEP
        s = s & "Y" & SEP ' Service Item
        s = s & "8" & SEP ' Vendor #
        s = s & "" & SEP ' Minimum qty
        s = s & "EA" & SEP ' UOM
        s = s & "1" & SEP ' Pack Measure
        
        ' Long desc
        t = Trim(Myset!NameOfProduce1) & " " & Trim(Myset!ItemDesc1) & " " & Trim(Myset!ItemDesc2)
        t = t & " " & Trim(Myset!ItemNutrition) & " " & plu
        t = t & " NuVal Score: " & Trim(Myset!NuValScore)
        If signtype = "C" Then
            t = t & " Conventional"
        End If
        If signtype = "H" Then
            t = t & " Homegrown"
        End If
        If signtype = "O" Then
            t = t & " Organic"
        End If
        t = t & " Produce Sign"
        s = s & t & SEP
        
        ' Image filename
        image = pathpre & plu & ".jpg"
        s = s & image & SEP
        
        s = s & "3" & SEP ' Max order qty
        s = s & "" & SEP ' Expiration Date
        
        cost=1.59
        cost = FormatNumber(cost, 2)
        s = s & cost & SEP
        
        s = s & "003" & SEP ' Key
        s = s & "N" ' Discontinued
        Print #2, s
        
        outcnt = outcnt + 1 ' Count records written.
    '...
        Myset.MoveNext
        i = i + 1
        If i / 10 = Int(i / 10) Then
          Call gStatusBar(i & " of " & cnt)
        End If
    Loop
 
No, the loop lets it gather all the records. I am inserting into a temp table then spooling the content out to Excel. I think you are trying to send directly to Excel via VBA, right? You may want to consider a temp table, simular to what I do. but you should also check out Bob's examples. He has helped me more than you can imagine!
 
Any calculation made in code, can be made in a query.

Then simply use transferspreadsheet to export the query, your comment of "will not meet my needs. " is very narrow minded .... Open your mind to new possibilities dude!
 
Any calculation made in code, can be made in a query.
Click to expand...
Doesn't Access have a limit on the number of characters in a query field? Can I enter 5000 characters in this query field? I also need to do looping to check other things against the field value, as well as substrings on the field, plus concatenations.

In my application I import a spreadsheet from a customer, which is not normalized. Then I later output the data from Access to the spreadsheet, in the exact same denormalized format. Thus my need for relatively complex looping and calculations.

I can't control what the customer sends, but I do have to deal with it.

Bob,
I think your code might work. But I noticed the following objects were not closed or set to "Nothing": ApXL, xlWbk, xlWsh. Isn't it required to do that?
 
bulrush said:
Bob,
I think your code might work. But I noticed the following objects were not closed or set to "Nothing": ApXL, xlWbk, xlWsh. Isn't it required to do that?
Click to expand...

If you do it, I think you'll see the Excel Workbook and instance that is currently open after you do this will disappear. Dealing with those variables is not really necessary because it will be cleaned up but if you close them and set them to nothing then the item that is open (remember I left the application open with the spreadsheet showing) will close. But, if it helps, give it a try and see what occurs. Maybe I'm wrong.
 
Instead of this:
xlWSh.Range("A2").CopyFromRecordset rst

Can I set the value of a specific cell as I loop through each record, like this?
xlWSh.Range("A2") = myvalue

where A2 is the cell I would manually keep track of in my code.


As you can see, this Excel stuff is new to me.

 
You must log in or register to reply here.

Similar threads

C
Opening "corrupt" Excel files
Replies
14
Views
1,270
Isaac
Isaac
S
REPORT TO EXCEL FORMAT
Replies
8
Views
1,099
Gasman
Gasman
C
Solved problem opening a .csv file in excel - dates all messed up
2
Replies
23
Views
3,166
CJ_London
C
M
Export 2 forms to same spreadsheet
Replies
7
Views
926
mounty76
M
oxicottin
Solved Import from excel and delete unwanted data
Replies
14
Views
1,764
oxicottin
oxicottin

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom