Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

access to excel

Posted on 2001-06-30
11
Medium Priority
?
143 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
Independent Software Vendors: 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

636 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