Solved

access to excel

Posted on 2001-06-30
11
140 Views
Last Modified: 2006-11-17
Hi!

I have access table (150 columns x 7000 records) and i want to have command that user can run and make xls file (export data from access to excel file).

Can anyone help with code?

Mitja
0
Comment
Question by:Mitja100
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +4
11 Comments
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6241470
If you have opened an Access application throght automation and the object variable is called accapp you could use code silmilar to this:

accapp.DoCmd.DoMenuItem 'use option export to an extern database and pass the name of an Excel woorbook, table name and so on.
Hope it helps
0
 
LVL 12

Expert Comment

by:Amick
ID: 6241490
Option Compare Database
Option Explicit

'------------------------------------------------------------
' Export_To_Excel - VBA
'
'------------------------------------------------------------
Function Export_To_Excel()
On Error GoTo Export_To_Excel_Err

    DoCmd.TransferSpreadsheet acExport, 8, "MyTable", "MyExcelFile.XLS", False, ""


Export_To_Excel_Exit:
    Exit Function

Export_To_Excel_Err:
    MsgBox Error$
    Resume Export_To_Excel_Exit

End Function


0
 

Author Comment

by:Mitja100
ID: 6242000
Ups!

I forgot to mention that access is not installed on computer where this app. will run, but excel is present.

Sorry!
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Mitja100
ID: 6242013
I found this and works just fine!

   Dim xlApp As Excel.Application
   Dim xlbook As Excel.Workbook
   Dim xlSheet As Excel.Worksheet
   Dim rs As Recordset
   Dim Counter As Integer


   Set xlApp = Excel.Application
   xlApp.Visible = True
   Set xlbook = xlApp.Workbooks.Open("c:\junk.xls")
   
   sql_caluse = "SELECT * FROM my_table "
    Set rs = DataEnvironment1.Connection1.Execute(sql_clause)
    rs.MoveFirst
   
   Set xlSheet = xlbook.Worksheets("Sheet1")
   Do Until rs.EOF
      Counter = Counter + 1
      With xlSheet
         .Cells(Counter, 1).Value = rs("Field1")
         .Cells(Counter, 2).Value = rs("Field2")
         .Cells(Counter, 3).Value = rs("Field3")
      End With
      rs.MoveNext
   Loop
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6242066
Hi Mitja100,

If Excel is not installed on the machine, then I'm not sure that your method will work propperly.

Try using the OutputTo method instead:

DoCmd.OutputTo acOutputTable, "TableName", acFormatXLS, "MyTable.xls", True

Hope this helps,

Nosterdamus



0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6242068
Sorry, didn't notice Excel is present and Access is NOT...

Please ignore my last comment.

Nosterdamus
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6242382
Create a ADODB recordset containing your data and pass your recordset to this sub that will generate a CSV file (that Excel recognize natively):

Public Sub CopyRsToCSV(ByVal prstData As ADODB.Recordset, Optional ByVal pstrFileName As String)
Dim intFileNumber As Integer
Dim lngLoop As Long
Dim strX As String

    If Len(Trim$(pstrFileName)) = 0 Then pstrFileName = App.Path & "\zzRst.csv"
   
    'Open data file data to file
    intFileNumber = FreeFile   ' Get unused file
    Open pstrFileName For Output As #intFileNumber
    'Write data to file
    With prstData
        strX = ""
        For lngLoop = 0 To .Fields.Count - 1
            strX = strX & .Fields(lngLoop).Name & ", "
        Next lngLoop
        Print #intFileNumber, strX
        If .RecordCount > 0 Then .MoveFirst
        Do Until .EOF
            strX = ""
            For lngLoop = 0 To .Fields.Count - 1
                strX = strX & .Fields(lngLoop).Value & ", "
            Next lngLoop
            Print #intFileNumber, strX
            .MoveNext
        Loop
    End With
    'Close
    Close #intFileNumber
End Sub
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6243068
It uses DAo engine to get the records:

Option Explicit
'set a reference to microsoft DAO 3.51 or 3.6 object library
'set areference to excel 8 object library
Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim sh As Excel.Worksheet

Sub SaveXlsFile(sFileName As String)
xl.ActiveWorkbook.SaveAs sFileName
End Sub
Private Sub Form_Load()
Set xl = New Excel.Application
' open excel
With xl
    .Visible = True
    Set wb = .Workbooks.Add
    Set sh = .ActiveWorkbook.ActiveSheet
End With

'open database
Set db = DBEngine.Workspaces(0).OpenDatabase("C:\Microsoft Visual Studio\VB98\nwind.mdb", False, False)
' populates recordset
Set rs = db.OpenRecordset("Clientes", dbOpenTable)
' show records on activesheet
sh.Range("a1").CopyFromRecordset rs

SaveXlsFile "c:\yourchoice.xls"
xl.Quit
Set xl = Nothing
Set rs = Nothing
db.Close
Set db = Nothing
End Sub




0
 
LVL 4

Expert Comment

by:wileecoy
ID: 6915939
Mitja100, any progress on this?
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 7035326
For Mitja100

It's time to resolve this question, so I will leave a recommendation in Community Support Topic Area that this question is:
- PAQ'd and points refunded

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Thanks!

Nosterdamus
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 7084252
Per recommendation, points refunded and question closed.

Netminder
CS Moderator
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question