?
Solved

Export to Excel

Posted on 2003-03-07
11
Medium Priority
?
479 Views
Last Modified: 2008-02-01
I have a table in Access called PCGcode I want to export to an Excel file C:\Documents and Settings\My Documents\Export.xls. I want the data to go into a worksheet called PCGNames (which is in Export.xls).
I can export the data to Excel without any problem but can't see how to export it to an existing worksheet.
Can anyone help.
Doobs
0
Comment
Question by:Doobs
[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
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 12

Accepted Solution

by:
nexusnation earned 200 total points
ID: 8087984
try using the TransferSpreadsheet Action (in a macro) or the TransferSpreadsheet Method (in VBA). you can choose the filename of the excel file and the range as well.

need more info?

nex
0
 
LVL 1

Author Comment

by:Doobs
ID: 8088117
Nex
You can't use the Range of the TransferSpreadsheet method to export.
Doobs
p.s. Got in a different way. Still appreciate answer to personal email if pos. (Ignore p.s. if this doesn't make any sense).
0
 
LVL 1

Expert Comment

by:ocsurf
ID: 8088626
Here is the whole Code you need, this is what i uses to do the same thing that you need. it is set to a button called "Command0". also you have to change ,<you table here>, you your own table.
---------Code----------
Option Compare Database
Private Declare Function GetOpenFileName _
 Lib "COMDLG32.DLL" Alias "GetOpenFileNameA" _
 (pOpenfilename As OPENFILENAME) As Long
Private Declare Function GetSaveFileName _
 Lib "COMDLG32.DLL" Alias "GetSaveFileNameA" _
 (pOpenfilename As OPENFILENAME) As Long
'Both functions require a single parameter pointing to an OPENFILENAME structure. In VBA, you format Windows structures as a user-defined type (UDT). FIGURE 4 shows how to create this data type in VBA.

Private Type OPENFILENAME
 lStructSize       As Long
 hwndOwner         As Long
 hInstance         As Long
 lpstrFilter       As String
 lpstrCustomFilter As String
 nMaxCustFilter    As Long
 nFilterIndex      As Long
 lpstrFile         As String
 nMaxFile          As Long
 lpstrFileTitle    As String
 nMaxFileTitle     As Long
 lpstrInitialDir   As String
 lpstrTitle        As String
 Flags             As Long
 nFileOffset       As Integer
 nFileExtension    As Integer
 lpstrDefExt       As String
 lCustData         As Long
 lpfnHook          As Long
 lpTemplateName    As String
End Type

Private Const OFN_FILEMUSTEXIST = &H1000
Private Const OFN_HIDEREADONLY = &H4
Private Const OFN_OVERWRITEPROMPT = &H2
Private Const OFN_PATHMUSTEXIST = &H800
Private Const OFN_SAVE = 0
Private Const OFN_OPEN = 1

Private Sub Command0_Click()
Dim ExcelSheet As Object
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.WorkSheet
Dim rst As Recordset
Dim size, n, m, i As Integer
Dim strFile As String
Static slngCount As Long
Static sdb As Database
Dim j, max As Long
Dim tdf As TableDef
Dim rsQdf As Recordset
Dim fld As Field

strFile = GetFile()

Set ExcelSheet = CreateObject("Excel.Sheet")
n = 1
m = 1
'This code starts the application creating the object, in this case, a Microsoft Excel spreadsheet. Once an object is created, you reference it in code using the object variable you defined. In the following example, you access properties and methods of the new object using the object variable, ExcelSheet, and other Microsoft Excel objects, including the Application object and the Cells collection.
' Make Excel visible through the Application object.

ExcelSheet.Application.Visible = True
   
' Place some text in the first cell of the sheet.
'max = UBound(sastrFields)

Dim temp As String
Set rst = CurrentDb.OpenRecordset("<you table here>", dbOpenDynaset)
rst.MoveLast
size = rst.RecordCount
rst.MoveFirst


For i = 0 To size - 1
    For Each fld In rst.Fields
    Me.SetFocus
      temp = fld.Value
      ExcelSheet.Application.Cells(m, n).Value = temp
      n = n + 1
    Next fld

rst.MoveNext
n = 1
m = m + 1
Next


'
 '   ExcelSheet.Application.Cells(n, 1).Value = temp
  '  rst.MoveNext
   ' n = n + 1
'
rst.CLOSE
ExcelSheet.Application.Sheets("Sheet1").Select
ExcelSheet.Application.Sheets("Sheet1").NAME = "PCGNames"
ExcelSheet.SaveAs strFile

' Close Excel with the Quit method on the Application object.

ExcelSheet.Application.Quit
End Sub
Public Function GetFile()
 Dim OFN As OPENFILENAME
 With OFN
   .lStructSize = Len(OFN)     ' Size of structure.
   .nMaxFile = 260             ' Size of buffer.
   .lpstrFilter = "Excel Files" & Chr$(0) & "*.xls" & Chr$(0) & "All Files" & Chr$(0) & "*.*"
   ' Create buffer.
   .lpstrFile = String(.nMaxFile - 1, 0)
    .Flags = OFN_HIDEREADONLY Or OFN_OVERWRITEPROMPT Or OFN_PATHMUSTEXIST
    x = GetSaveFileName(OFN)
     
    If x <> 0 Then
      If InStr(.lpstrFile, Chr$(0)) > 0 Then
        szFile = Left$(.lpstrFile, InStr(.lpstrFile, Chr$(0)) - 1)
        GetFile = szFile
      End If
    Else
      MsgBox "There was an error"
      GetFile = ""
    End If
 End With
End Function
--------end code-------
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 2

Expert Comment

by:NevHolland
ID: 8088719
Doobs,

Why can't you use the range criteria to export?

I've used it before.

0
 
LVL 1

Expert Comment

by:ocsurf
ID: 8088861
one other note...make sure your references are set to

visual basic app
ms access 9.0
ole automation
ms dao 3.6
ms visual basic for app exten 5.3
ms excel 9.0 object libary

0
 
LVL 12

Expert Comment

by:nexusnation
ID: 8090214
doobs, you can indeed use range. i have no clue what you are talking about... :-)

and i did respond to the e-mail...
0
 
LVL 12

Expert Comment

by:nexusnation
ID: 8098456
ocsurf, that is a lot of work. you can just use ONE macro action or ONE vba method...

nex
0
 
LVL 1

Author Comment

by:Doobs
ID: 8101881
Hi Guys,
Just a quick note to say thanks for your comments so far. I'm still working on it using your comments and code. I'll get back to you soon.
Doobs
0
 
LVL 1

Author Comment

by:Doobs
ID: 8101960
I've managed to get the transferespreadsheet message going.The reason why I didn't think you could use the range argument was that it said in the help file you can't use it for exporting. Obviously this is wrong and you can. Thanks for your help.
Ocsurv: Even though I didn't use the code you sent me it was really useful to see what can be done. I'm going the post a question called "Points for Ocsurv" with the points for this code.
Yours
Doobs
0
 

Expert Comment

by:AnupamDG
ID: 8656031
Doobs,
Send me a copy of the code. I have a similar problem and i need to copy new data on an existing excel sheet everyday so that all the other reports and Macro work correctly.

Anupam
0
 
LVL 1

Author Comment

by:Doobs
ID: 8656549
Sub testOpenFile4()
Dim oApp1 As Object
Dim oApp2 As Object

Set oApp1 = CreateObject("Excel.Application")
Set oApp2 = CreateObject("Excel.Application")
'FileName , UpdateLinks, ReadOnly
oApp1.Workbooks.Open FileName:="C:\Prescribing\TestExport\HounQalTrends_Q4_2003.xls", ReadOnly:=False

oApp2.Workbooks.Open "C:\Prescribing\TestExport\BCIQalTrends_Q4_2003.xls", , -1
oApp1.Visible = True
oApp2.Visible = True
oApp1.Application.Cells(2, 2).Select
oApp1.Application.Cells(2, 2).Value = "Hello1"
oApp2.Application.Cells(2, 2).Select
oApp2.Application.Cells(2, 2).Value = "Hello1"

'To quit write:
'oApp1.Application.Save
'oApp2.Application.Save
oApp1.Application.Quit
oApp2.Application.Quit

End Sub
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

771 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