473,836 Members | 1,254 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Discussion: Fully customized export data from Access to Excel

759 Contributor
Hello !
I read a lot about transfer data from Access to Excel.
However I have not yet, in my brain, a clear stack. So, I ask you to help/assist me.
Thank you !

All work must be done in Access environment.
So:

Step 1. Create an Excel workbook.
How to create an Excel workbook at a certain location (folder) and with a certain name: "ExcelWorkB ook" ?
Feb 20 '12 #1
15 6426
NeoPa
32,584 Recognized Expert Moderator MVP
This could be handled one of two ways, but the issues surrounding it are various. This is really a multi-faceted question rather than a discussion Mihail, but let's give some basic pointers and you can take them further where required :
  1. DoCmd.TransferS preadsheet() is one approach.
  2. Application Automation is another.
#2 can be very helpful if you want more than a simple data dump. #1 can handle specification of the worksheet name, but only by naming the Access object that's transferred commensurately.

Determining exactly where a file should be stored can be done in so many ways, depending on your requirements, but Select a File or Folder using the FileDialog Object and Does a File Exist? may help there.
Feb 20 '12 #2
Mihail
759 Contributor
Thank you for that, NeoPa.
After I am able to create an workbook from Access I'll return here for the next step.
If I can't do the task I'll return here anyway :).
Feb 20 '12 #3
NeoPa
32,584 Recognized Expert Moderator MVP
Sounds good to me Mihail. I'm sure you'll manage it. One way or another :-)
Feb 20 '12 #4
Mihail
759 Contributor
Step 1: Accomplished

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. ' Requires a Reference to Microsoft Excel 8.0 Object Library or Higher
  5. ' In VBE, goto Tools->References... and select it from the list
  6.  
  7. ' Variable Declarations
  8. Public objExcel As Excel.Application
  9.  
  10. Public Sub Test()
  11.     Call CreateExcelObject
  12.     Call AddWorkbook(True)
  13.     Call SaveWorkbook("C:\CreateExcelWorkbook_Test.xlsx")
  14.     Call QuiteExcelObject
  15. End Sub
  16.  
  17. Public Function CreateExcelObject() As Boolean
  18.     CreateExcelObject = False
  19. On Error GoTo ErrorHandler
  20.     ' If Excel is open, use GetObject, otherwise create a new Excel object
  21.     Set objExcel = GetObject(, "Excel.Application")
  22.     CreateExcelObject = True
  23.  
  24. Ex:
  25.  
  26. Exit Function
  27.  
  28. ErrorHandler:
  29.     Select Case Err.Number
  30.         Case 429 'Application not runing
  31.             Set objExcel = New Excel.Application
  32.             Resume Next
  33.         Case Else
  34.             MsgBox ("MyMesage   " & Err.Number & " " & Err.Description)
  35.             CreateExcelObject = False
  36.             Resume Ex
  37.     End Select
  38. End Function
  39.  
  40. Public Function QuiteExcelObject() As Boolean
  41. On Error Resume Next
  42.     objExcel.Quit
  43. End Function
  44.  
  45. Public Function AddWorkbook(Optional MakeVisible As Boolean) As Boolean
  46.     AddWorkbook = False
  47. On Error GoTo ErrorHandler
  48.  
  49.     With objExcel
  50.         ' Adds a new workbook to the Excel environment
  51.         .Workbooks.Add
  52.         ' Set the Excel window visibilitie
  53.         .Visible = MakeVisible
  54.     End With
  55.     AddWorkbook = True
  56.  
  57. Ex:
  58.  
  59. Exit Function
  60.  
  61. ErrorHandler:
  62.     Select Case Err.Number
  63.         Case 429 'Application not runing
  64.             MsgBox ("First you must create ExcelObject")
  65.         Case Else
  66.             MsgBox ("MyMesage   " & Err.Number & " " & Err.Description)
  67.     End Select
  68.  
  69.     Resume Ex
  70. End Function
  71.  
  72. Public Function SaveWorkbook(WkPath As String) As Boolean
  73.     SaveWorkbook = False
  74. On Error GoTo ErrorHandler
  75.     With objExcel
  76.         .ActiveWorkbook.SaveAs (WkPath)
  77.     End With
  78.     SaveWorkbook = True
  79.  
  80. Ex:
  81.  
  82. Exit Function
  83.  
  84. ErrorHandler:
  85.     MsgBox ("MyMesage   " & Err.Number & " " & Err.Description)
  86.     Resume Ex
  87. End Function
  88.  
Feb 24 '12 #5
Mihail
759 Contributor
Now
Step 2. Working with Excel sheets.

2.1 How to count the sheets.
2.2 How to see the sheets names, how to change this name ? Here there are two points: The caption and the real name (the name used with in VBA editor).

I think I can do the job but, if someone point me in the right direction I do not waste time.
Thank you !
Feb 24 '12 #6
MikeTheBike
639 Recognized Expert Contributor
Hi

Just added some code to youe subroutine to illustrate what is possible etc.
Expand|Select|Wrap|Line Numbers
  1. Public Sub Test()
  2.     Call CreateExcelObject
  3.     Call AddWorkbook(True)
  4.  
  5.     With objExcel
  6.         MsgBox .ActiveWorkbook.Sheets.Count
  7.         MsgBox .ActiveSheet.Name
  8.         .ActiveSheet.Name = "New Sheet Name"
  9.         MsgBox .ActiveSheet.Name
  10.  
  11.         Dim sht As Worksheet
  12.         For Each sht In .ActiveWorkbook.Sheets
  13.             MsgBox sht.Name
  14.         Next sht
  15.     End With
  16.  
  17.     Call SaveWorkbook("C:\CreateExcelWorkbook_Test.xlsx")
  18.     Call QuiteExcelObject
  19. End Sub
There is also a Workbooks collection that you can also loop through in a similar way.

This may raise more questions, but hopefully not!


MTB
Feb 24 '12 #7
Mihail
759 Contributor
:) Thanks Mike.
From now is a little bit of work to design some specific routines.
After that I'll return for next steps.
Thank you again !
Feb 24 '12 #8
NeoPa
32,584 Recognized Expert Moderator MVP
For step #1 I would suggest that lines #40 to #43 might be redone as :
Expand|Select|Wrap|Line Numbers
  1. Public Function QuitExcelObject() As Boolean
  2. On Error Resume Next
  3.     Call objExcel.Quit
  4.     Set objExcel = Nothing
  5. End Function
The name had a strange spelling and objExcel was left as a refernce to a no-longer-valid object.

For step #2.1 I would simply point out that all collections have a .Count property.

For step #2.2 I would link you to a new article I just created for this situation - Excel Module (Code) Names.

Have fun :-)
Feb 24 '12 #9
Mihail
759 Contributor
Thank you for your advices. Very useful.
I update the code as you can see.

Now I have almost all tools to make export-import.
The last step is (I think) to format cells.
Not as Font or Pattern (it will be great but not very useful).
So, how to format cells as Text, Numbers(Integer , Single, Double) and as Date ?

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Msg As VbMsgBoxResult
  5.  
  6. ' Requires a Reference to Microsoft Excel 8.0 Object Library or Higher
  7. ' In VBE, goto Tools->References... and select it from the list
  8.  
  9. ' Variable Declarations
  10. Public objExcel As Excel.Application
  11.  
  12. Public Sub Test()
  13.     Call CreateExcelObject
  14.     Call AddWorkbook(True)
  15.  
  16.     With objExcel
  17.         Msg = MsgBox("ActiveWorkbookName = " & .ActiveWorkbook.Name)
  18.  
  19.         'Count the sheets
  20.         Msg = MsgBox("Sheets.Count = " & .ActiveWorkbook.Sheets.Count)
  21.  
  22.         'Rename Active Sheet
  23.         .ActiveSheet.Name = "SheetNo1"
  24.         Msg = MsgBox("ActiveSheet.Name = " & .ActiveSheet.Name)
  25.  
  26.         'Rename a sheet
  27.         .Sheets(2).Name = "SheetNo2"
  28.         Msg = MsgBox(".Sheets(2).Name = " & .Sheets(2).Name)
  29.  
  30.         'Activate a random sheet
  31.         .Sheets("SheetNo2").Activate
  32.         Msg = MsgBox("NewActiveSheet.Name = " & .ActiveSheet.Name)
  33.  
  34.         'Remove a sheet
  35.         .ActiveSheet.Delete
  36.         Msg = MsgBox("ActiveSheetAfterDeletion.Name = " & .ActiveSheet.Name)
  37.  
  38.         'Write to Excel in Active Sheet
  39.         .ActiveSheet.Cells(2, 3) = "This will be write in Active Sheet"
  40.         'Read from Excel from Active Sheet
  41.         Msg = MsgBox("ReadFromExcel = " & .ActiveSheet.Cells(2, 3))
  42.  
  43.         'Write to Excel in a random Sheet
  44.         .Sheets("SheetNo1").Cells(1, 1) = "This will be write in sheet named ""SheetNo1"""
  45.         'Read from Excel from a random Sheet
  46.         Msg = MsgBox("ReadFromExcel = " & .Sheets("SheetNo1").Cells(1, 1))
  47.  
  48. '        Dim sht As Worksheet
  49. '        For Each sht In .ActiveWorkbook.Sheets
  50. '            Msg = MsgBox("Sheet(" & sht.Index & ").Name = " & sht.Name)
  51. '        Next sht
  52.     End With
  53.  
  54. On Error Resume Next
  55.     Kill ("C:\CreateExcelWorkbook_Test.xlsx")
  56.         Call SaveWorkbook("C:\CreateExcelWorkbook_Test.xlsx")
  57.         Msg = MsgBox("NewWorkbookName = " & objExcel.ActiveWorkbook.Name)
  58.         Call QuiteExcelObject
  59. End Sub
  60.  
  61. Public Function CreateExcelObject() As Boolean
  62.     CreateExcelObject = False
  63. On Error GoTo ErrorHandler
  64.     ' If Excel is open, use GetObject, otherwise create a new Excel object
  65.     Set objExcel = GetObject(, "Excel.Application")
  66.     CreateExcelObject = True
  67.  
  68. Ex:
  69.  
  70.     Exit Function
  71.  
  72. ErrorHandler:
  73.     Select Case Err.Number
  74.     Case 429 'Application not runing
  75.         Set objExcel = New Excel.Application
  76.         Resume Next
  77.     Case Else
  78.         MsgBox ("MyMesage   " & Err.Number & " " & Err.Description)
  79.         CreateExcelObject = False
  80.         Resume Ex
  81.     End Select
  82. End Function
  83.  
  84. Public Function QuiteExcelObject() As Boolean
  85. On Error Resume Next
  86.     Call objExcel.Quit
  87.     Set objExcel = Nothing
  88. End Function
  89.  
  90. Public Function AddWorkbook(Optional MakeVisible As Boolean) As Boolean
  91.     AddWorkbook = False
  92.     On Error GoTo ErrorHandler
  93.  
  94.     With objExcel
  95.         ' Adds a new workbook to the Excel environment
  96.         .Workbooks.Add
  97.         ' Set the Excel window visibilitie
  98.         .Visible = MakeVisible
  99.     End With
  100.     AddWorkbook = True
  101.  
  102. Ex:
  103. Exit Function
  104.  
  105. ErrorHandler:
  106.     Select Case Err.Number
  107.         Case 429 'Application not runing
  108.             MsgBox ("First you must create ExcelObject")
  109.         Case Else
  110.             MsgBox ("MyMesage   " & Err.Number & " " & Err.Description)
  111.     End Select
  112.  
  113.     Resume Ex
  114. End Function
  115.  
  116. Public Function SaveWorkbook(WkPath As String) As Boolean
  117.     SaveWorkbook = False
  118. On Error GoTo ErrorHandler
  119.     With objExcel
  120.         .ActiveWorkbook.SaveAs (WkPath)
  121.     End With
  122.     SaveWorkbook = True
  123.  
  124. Ex:
  125. Exit Function
  126.  
  127. ErrorHandler:
  128.     MsgBox ("MyMesage   " & Err.Number & " " & Err.Description)
  129.     Resume Ex
  130. End Function
Mar 16 '12 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

0
4769
by: Funbeat | last post by:
Hi everybody, I'm facing with the following problem (bug ?) : A page is calling another one (export.aspx) for exporting data to excel. The tecnhique used is to create a Excel-MIME stream for viewing it directly in the browser. (below is a sample code)
5
4095
by: Jonny | last post by:
Hello, I have created a button on my form which when pressed does the following : 1) Run a pre-defined Macro, which is picking up a query and running my data to excel. However, I need the data to export into Excel in a certain format, i.e it needs to begin importing at cell A4, and in truth it would be great
4
4644
by: Agnes | last post by:
I can export the data to excel, but it is really really slow. need 5-6 mins to export 30 fields (a hundred records) . for my old vfp application, less than 3 minutes. for 500-800 records. Does any idea to improve the alog ?? Or what Can I do in my sql server ? Thanks a lot 'For displaying the column name in the the excel file. For intColumn = 0 To dsExcelExport.Tables(0).Columns.Count - 1 .Cells(1, intColumn + 1).Value =...
1
1821
by: C | last post by:
Hi, Does anyone know of any sites that have sample code that shows how to export data to Excel using VS Tools for Office? What is the advantage of using VS Tools for Office over uisng Interop? Thanks.
5
40784
by: bimalkumar | last post by:
hi 2 all, i m using some txt boxes and few combo box in my form. how do i export the txt box and combobox data to excel sheet directly without using any database.just get the data from txt box and combo box and export it to excel. while answering pls remember i m not regular VB coder. I just do it for some small application. pls help me.
7
7819
by: Vanessa | last post by:
hi Everyone, I have two questions on exporting data to Excel using ASP (w/o converting formatted excel file into web page and then plug in the dynamic data): 1. Can we export data into different sheets at Excel? 2. Can we do graph at export? Thanks!!
1
2660
by: forumaic | last post by:
Hello, I am trying to export data to excel from datagrid, and I am getting an error: "The Controls collection cannot be modified because the control contains code blocks (i.e. <% ... %>)." Error details: System.Web.HttpException was unhandled by user code Message="The Controls collection cannot be modified because the control
2
5563
by: hal | last post by:
Hello all, I've been searching all day for an article or tutorial on how to get data from a SQL Server 2000 database and export the data to excel 2003 so that multiple worksheets are created, and information is displayed in these worksheets. Data will be coming from multiple tables, and I'm using C# and asp.net 2.0. User will click on an asp.net button to pull the data and put it in an excel spreadsheet. Thanks
2
2060
by: teneesh | last post by:
I have the following query behind a button in Access. But I'd like for this button, after running the query to export this data to excel. When I look this up online, I find nothing, not sure if I'm explaining it correctly in order to receive the right answers. Here's the query - how do i add a command to export it to excel? sqltext = "SELECT i.AcYear, i.Eid, i.EidStaffCode, i.EvalteeID, e.Evalrecno, dbo.Clinic.clinicname, " & _ ...
2
4337
by: kashif73 | last post by:
Hi, I want to export data from 4 Access tables to Excel using VBA. The user selects 2 different DATES from the form & based on DATEs criteria all record from 4 tables should be extracted to excel. I can extract data from Table 1 to excel, which has the DATE field. But other 3 tables doesn't have this DATE field. I was wondering how can I get data in Excel from other 3 tables?? All 4 tables have 1 field in common "REFNO". This field is the...
0
9813
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10829
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10535
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10245
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9365
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7778
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5645
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4446
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4005
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.