Export data from Access to Excel then open a macro (1 Viewer)

LeslyP

Registered User.
Local time
Today, 12:28
Joined
Apr 27, 2018
Messages
34
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 :)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:28
Joined
Oct 29, 2018
Messages
21,358
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?
 

LeslyP

Registered User.
Local time
Today, 12:28
Joined
Apr 27, 2018
Messages
34
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:28
Joined
Oct 29, 2018
Messages
21,358
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
 

LeslyP

Registered User.
Local time
Today, 12:28
Joined
Apr 27, 2018
Messages
34
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()

[COLOR="Red"]HERE ?[/COLOR]

 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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:28
Joined
Oct 29, 2018
Messages
21,358
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!
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:28
Joined
Sep 21, 2011
Messages
14,046
PMFJI,

Have you tried something along the lines of

Code:
objXLS.run Prep_R

https://access-excel.tips/run-excel-macro-from-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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:28
Joined
Oct 29, 2018
Messages
21,358
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!
 

LeslyP

Registered User.
Local time
Today, 12:28
Joined
Apr 27, 2018
Messages
34
PMFJI,

Have you tried something along the lines of

Code:
objXLS.run Prep_R

https://access-excel.tips/run-excel-macro-from-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 ^^
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:28
Joined
Oct 29, 2018
Messages
21,358
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.
 

LeslyP

Registered User.
Local time
Today, 12:28
Joined
Apr 27, 2018
Messages
34
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:
[B]Directly write the Excel formatting function in Access[/B]

Public Function runExcelMacro(wkbookPath)
    Dim XL As Object
    Set XL = CreateObject("Excel.Application")
    With XL
        .Visible = False
        .displayalerts = False
        .Workbooks.Open wkbookPath
  [COLOR="Blue"]      'Write your Excel formatting, the line below is an example[/COLOR]
        .Range("C2").value = "=1+2"
        .ActiveWorkbook.Close (True)
        .Quit
    End With
    Set XL = Nothing
End Function
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:28
Joined
Oct 29, 2018
Messages
21,358
The link Gasman gave me is really nice. You can also write you macro directly in Access !

Code:
[B]Directly write the Excel formatting function in Access[/B]

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

Gasman

Enthusiastic Amateur
Local time
Today, 19:28
Joined
Sep 21, 2011
Messages
14,046
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. :D



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.
 

LeslyP

Registered User.
Local time
Today, 12:28
Joined
Apr 27, 2018
Messages
34
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:28
Joined
Oct 29, 2018
Messages
21,358
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!
 

Users who are viewing this thread

Top Bottom