Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-24-2019, 06:19 AM   #1
LeslyP
Newly Registered User
 
Join Date: Apr 2018
Posts: 34
Thanks: 16
Thanked 2 Times in 2 Posts
LeslyP is on a distinguished road
Export data from Access to Excel then open a macro

Hi everyone,

My problem is about Excel and Access, so I was not sure where to ask it.

In my Access database, there is a button to export the data in a Excel sheet. No problem for that. But then, I want a simple macro to start on the Excel sheet to "clean it" for analysis purpose.

How do I do that ? Should I program it in Access with the export code (If yes, my post should be transfert in the Access section) or sould I give a macro to my users so they can download it and just clic on it when needed (if yes, how sould I do) ?

Thank you

LeslyP is offline   Reply With Quote
Old 10-24-2019, 07:20 AM   #2
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,540
Thanks: 58
Thanked 1,429 Times in 1,410 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Export data from Access to Excel then open a macro

Hi. Since I am no Excel person, let me just ask you this first. Can't you "clean" the data within Access first before exporting it to Excel?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
LeslyP (10-24-2019)
Old 10-24-2019, 07:31 AM   #3
LeslyP
Newly Registered User
 
Join Date: Apr 2018
Posts: 34
Thanks: 16
Thanked 2 Times in 2 Posts
LeslyP is on a distinguished road
Re: Export data from Access to Excel then open a macro

Hi theDBguy

Thank you for you fast answer.

Hummm, good question. I do not know if we can do that in Access.

What I want to do is :
1. Change the spaces for "_"
2. Change all the letter with accent like "" to no accent "e"
3. Delete all the " ' '
4.Fill the empty space with "NA"

Can we do that in a Recordset before export ?

Here is the code I use:

Code:
Private Sub Exporter_Click()
 Dim objXLS As Object
 Dim wks As Object
 Dim rsc As Recordset
 Dim idx As Long
 Set rsc = Me.RecordsetClone
 rsc.MoveLast
 rsc.MoveFirst
 Set objXLS = CreateObject("Excel.Application")
 objXLS.Workbooks.Add
 Set wks = objXLS.Worksheets(1)
 For idx = 0 To rsc.Fields.Count - 1
   wks.Cells(1, idx + 1).Value = rsc.Fields(idx).Name
 Next
 wks.Range(wks.Cells(1, 1), wks.Cells(1, rsc.Fields.Count)).Font.Bold = True
 wks.Range("A2").CopyFromRecordset rsc, rsc.RecordCount, rsc.Fields.Count
 objXLS.Visible = True
 Set objXLS = Nothing
End Sub

LeslyP is offline   Reply With Quote
Old 10-24-2019, 07:36 AM   #4
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,540
Thanks: 58
Thanked 1,429 Times in 1,410 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Export data from Access to Excel then open a macro

Quote:
Originally Posted by LeslyP View Post
Hi theDBguy

Thank you for you fast answer.

Hummm, good question. I do not know if we can do that in Access.

What I want to do is :
1. Change the spaces for "_"
2. Change all the letter with accent like "" to no accent "e"
3. Delete all the " ' '
4.Fill the empty space with "NA"

Can we do that in a Recordset before export ?
How about?
1. Replace([FieldName]," ","_")
2. I'll have to check on this one. Can you show your Excel macro that does this? Maybe we can translate it into Access VBA.
3. Replace([FieldName],"'","")
4. UPDATE TableName SET FieldName="NA" WHERE FieldName Is Null
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
LeslyP (10-24-2019)
Old 10-24-2019, 08:00 AM   #5
LeslyP
Newly Registered User
 
Join Date: Apr 2018
Posts: 34
Thanks: 16
Thanked 2 Times in 2 Posts
LeslyP is on a distinguished road
Re: Export data from Access to Excel then open a macro

Ok so here is my macro:

Code:
Sub Prep_R()
'
' Prep_R Macro
'

'
    Selection.End(xlToLeft).Select
    Selection.End(xlUp).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Replace What:=" ", Replacement:="_", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="", Replacement:="e", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="", Replacement:="e", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="", Replacement:="e", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="", Replacement:="a", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="o", Replacement:="ou", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="", Replacement:="c", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="", Replacement:="e", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="'", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="", Replacement:="NA", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub
Where do I add the new code ? My guess would be defore ?

Code:
Private Sub Exporter_Click()

HERE ?

 Dim objXLS As Object
 Dim wks As Object
 Dim rsc As Recordset
 Dim idx As Long
 Set rsc = Me.RecordsetClone
 rsc.MoveLast
 rsc.MoveFirst
 Set objXLS = CreateObject("Excel.Application")
 objXLS.Workbooks.Add
 Set wks = objXLS.Worksheets(1)
 For idx = 0 To rsc.Fields.Count - 1
   wks.Cells(1, idx + 1).Value = rsc.Fields(idx).Name
 Next
 wks.Range(wks.Cells(1, 1), wks.Cells(1, rsc.Fields.Count)).Font.Bold = True
 wks.Range("A2").CopyFromRecordset rsc, rsc.RecordCount, rsc.Fields.Count
 objXLS.Visible = True
 Set objXLS = Nothing
End Sub
LeslyP is offline   Reply With Quote
Old 10-24-2019, 08:05 AM   #6
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,540
Thanks: 58
Thanked 1,429 Times in 1,410 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Export data from Access to Excel then open a macro

Quote:
Originally Posted by LeslyP View Post
Ok so here is my macro:
...
Where do I add the new code ? My guess would be defore ?
Ah, so you're basically just searching for each specific character and replacing with its equivalent. I though you might have some sort of algorithm that uses some mathematical equation to determine which characters to fix. In that case, you can simply use the Replace() function as well. So, yes, the clean up process can be completed within Access, and you won't have to worry about how to execute the Excel macro because it won't be needed. And yes, you would put the clean up process before the export code. Good luck!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
LeslyP (10-24-2019)
Old 10-24-2019, 08:31 AM   #7
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,527
Thanks: 441
Thanked 841 Times in 812 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Export data from Access to Excel then open a macro

PMFJI,

Have you tried something along the lines of

Code:
objXLS.run Prep_R
https://access-excel.tips/run-excel-...om-access-vba/

The only reason I suggest it, is because I think it would be

a. Easier
b. More efficient

Otheriwse I think you will have to apply the Replace code for every field in the recordset?, whereas Excel is doing the whole block os cells?

Just a thought.

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
The Following 2 Users Say Thank You to Gasman For This Useful Post:
LeslyP (10-24-2019), theDBguy (10-24-2019)
Old 10-24-2019, 08:36 AM   #8
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,540
Thanks: 58
Thanked 1,429 Times in 1,410 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Export data from Access to Excel then open a macro

Quote:
Originally Posted by Gasman View Post
Otheriwse I think you will have to apply the Replace code for every field in the recordset?, whereas Excel is doing the whole block os cells?

Just a thought.
Ah, good point. This shows my lack of Excel knowledge. Thanks!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-24-2019, 08:50 AM   #9
LeslyP
Newly Registered User
 
Join Date: Apr 2018
Posts: 34
Thanks: 16
Thanked 2 Times in 2 Posts
LeslyP is on a distinguished road
Thumbs up Re: Export data from Access to Excel then open a macro

Quote:
Originally Posted by Gasman View Post
PMFJI,

Have you tried something along the lines of

Code:
objXLS.run Prep_R
https://access-excel.tips/run-excel-...om-access-vba/

The only reason I suggest it, is because I think it would be

a. Easier
b. More efficient

Otheriwse I think you will have to apply the Replace code for every field in the recordset?, whereas Excel is doing the whole block os cells?

Just a thought.
Ah ! thank you so much. That's exatly what I was looking for.
Google was not with me for that one.

_____________


And thank you theDBguy for trying. It is nice of you ^^
LeslyP is offline   Reply With Quote
Old 10-24-2019, 09:02 AM   #10
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,540
Thanks: 58
Thanked 1,429 Times in 1,410 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Export data from Access to Excel then open a macro

Quote:
Originally Posted by LeslyP View Post
Ah ! thank you so much. That's exatly what I was looking for.
Google was not with me for that one.

_____________


And thank you theDBguy for trying. It is nice of you ^^
Hi. You're welcome. Good luck! However, looking at your original code, I don't see that you're opening an existing Excel file with this macro already in it, so you can run it. So, if you're creating a brand new Excel file to export the data, you might have to add something in your code to "add the macro" in the new Excel file before you can call it.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-24-2019, 09:06 AM   #11
LeslyP
Newly Registered User
 
Join Date: Apr 2018
Posts: 34
Thanks: 16
Thanked 2 Times in 2 Posts
LeslyP is on a distinguished road
Re: Export data from Access to Excel then open a macro

Quote:
Originally Posted by theDBguy View Post
Hi. You're welcome. Good luck! However, looking at your original code, I don't see that you're opening an existing Excel file with this macro already in it, so you can run it. So, if you're creating a brand new Excel file to export the data, you might have to add something in your code to "add the macro" in the new Excel file before you can call it.
The link Gasman gave me is really nice. You can also write you macro directly in Access !

Code:
Directly write the Excel formatting function in Access

Public Function runExcelMacro(wkbookPath)
    Dim XL As Object
    Set XL = CreateObject("Excel.Application")
    With XL
        .Visible = False
        .displayalerts = False
        .Workbooks.Open wkbookPath
        'Write your Excel formatting, the line below is an example
        .Range("C2").value = "=1+2"
        .ActiveWorkbook.Close (True)
        .Quit
    End With
    Set XL = Nothing
End Function
LeslyP is offline   Reply With Quote
Old 10-24-2019, 09:08 AM   #12
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,540
Thanks: 58
Thanked 1,429 Times in 1,410 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Export data from Access to Excel then open a macro

Quote:
Originally Posted by LeslyP View Post
The link Gasman gave me is really nice. You can also write you macro directly in Access !

Code:
Directly write the Excel formatting function in Access

Public Function runExcelMacro(wkbookPath)
    Dim XL As Object
    Set XL = CreateObject("Excel.Application")
    With XL
        .Visible = False
        .displayalerts = False
        .Workbooks.Open wkbookPath
        'Write your Excel formatting, the line below is an example
        .Range("C2").value = "=1+2"
        .ActiveWorkbook.Close (True)
        .Quit
    End With
    Set XL = Nothing
End Function
Excellent! Good luck with your project.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-24-2019, 09:41 AM   #13
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,527
Thanks: 441
Thanked 841 Times in 812 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Export data from Access to Excel then open a macro

Good point theDBguy,

The o/p could either use the same file and clear it each time, or copy an empty file as a template and use that.?

\i've not written any code in Excel from Access, nor run an Excel macro from Accessl to be honest, but thought it should be doable.

@LeslyP
Please post your final code. It might help others, including me.



Quote:
Originally Posted by theDBguy View Post
Hi. You're welcome. Good luck! However, looking at your original code, I don't see that you're opening an existing Excel file with this macro already in it, so you can run it. So, if you're creating a brand new Excel file to export the data, you might have to add something in your code to "add the macro" in the new Excel file before you can call it.
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 10-29-2019, 05:31 AM   #14
LeslyP
Newly Registered User
 
Join Date: Apr 2018
Posts: 34
Thanks: 16
Thanked 2 Times in 2 Posts
LeslyP is on a distinguished road
Re: Export data from Access to Excel then open a macro

Well... the code of the Excel macro is not the same as that of the Access Excel macro and I have not found any "translator" yet.
I'm going to take a break, but if I manage to make it work, I'll put it here for sure.
LeslyP is offline   Reply With Quote
Old 10-29-2019, 06:52 AM   #15
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,540
Thanks: 58
Thanked 1,429 Times in 1,410 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Export data from Access to Excel then open a macro

Quote:
Originally Posted by LeslyP View Post
Well... the code of the Excel macro is not the same as that of the Access Excel macro and I have not found any "translator" yet.
I'm going to take a break, but if I manage to make it work, I'll put it here for sure.
Looking forward to it. Cheers!

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
excel macro to export data from excel to ACCESS yojo Macros 1 06-09-2014 09:36 AM
[SOLVED] access export to excel thru vba but excel file Dumb on Open sumox Modules & VBA 1 06-04-2014 10:37 AM
export data from access to excel, do not open the exel file benjamin.grimm Modules & VBA 7 09-10-2013 07:29 AM
Access 2003 - Macro Command - Export to Excel krazykasper Macros 1 02-17-2010 06:21 AM
Macro to export access data to existing excel worksheet PRodgers4284 Macros 6 10-12-2009 04:14 PM




All times are GMT -8. The time now is 05:45 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World