By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,351 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Discussion: Fully customized export data from Access to Excel

100+
P: 759
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: "ExcelWorkBook" ?
Feb 20 '12 #1
Share this Question
Share on Google+
15 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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.TransferSpreadsheet() 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

100+
P: 759
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
Expert Mod 15k+
P: 31,186
Sounds good to me Mihail. I'm sure you'll manage it. One way or another :-)
Feb 20 '12 #4

100+
P: 759
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

100+
P: 759
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

Expert 100+
P: 634
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

100+
P: 759
:) 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
Expert Mod 15k+
P: 31,186
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

100+
P: 759
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

NeoPa
Expert Mod 15k+
P: 31,186
Mihail:
So, how to format cells as Text, Numbers(Integer, Single, Double) and as Date?
I suggest you set up a number of cells, each with a different format that you're interested in knowing about, then print the .Format property of each in the Immediate pane of the VBA IDE. That way you can see what format string is required for each.
Mar 16 '12 #11

100+
P: 759
This is what i am looking for. The syntax for Format instruction.
I think that I can make an idea after I record a macro in Excel. But, if someone already know how to and wish to share...
Mar 16 '12 #12

NeoPa
Expert Mod 15k+
P: 31,186
You don't need to record a macro. That's not what I said. Just format a cell (EG A1.) in any way that you're interested in knowing about, then go to the VBA IDE, and the Immediate Pane within that, and type :
Expand|Select|Wrap|Line Numbers
  1. ?Range("A1").Format
That takes less than half a minute. Alternatively I could print all the known possibilities which might take me 5 minutes. An easy choice I think.
Mar 16 '12 #13

100+
P: 759
Oh, thank you !
Indeed, I don't understand what you say in post #11.
I'll try and I'll return for update the code.

Thank you again.
Mar 17 '12 #14

NeoPa
Expert Mod 15k+
P: 31,186
Mihail:
Indeed, I don't understand what you say in post #11.
That makes sense. I do try to make my English more basic for you Mihail, but sometimes I forget and use more advanced (and complex) language, which is harder for you to understand.
Mar 17 '12 #15

100+
P: 759
I design a small program for importing data from Excel.
Of course you will find bugs and unhandled errors.
Please inform me about this and I'll try to solve it.
Thank you !
Attached Files
File Type: zip ImportFromExcel.zip (166.3 KB, 144 views)
Mar 22 '12 #16

Post your reply

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