Export data from Access to Excel then open a macro

L

LeslyP

Registered User.
Local time
Today, 14:45
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 :)
 
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?
 
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 said:
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 ?
Click to expand...
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
 
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
 
LeslyP said:
Ok so here is my macro:
...
Where do I add the new code ? My guess would be defore ?
Click to expand...
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!
 
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.
 
Gasman said:
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.
Click to expand...
Ah, good point. This shows my lack of Excel knowledge. Thanks!
 
Gasman said:
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.
Click to expand...

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 said:
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 ^^
Click to expand...
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.
 
theDBguy said:
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.
Click to expand...

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
 
LeslyP said:
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
Click to expand...
Excellent! Good luck with your project.
 
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



theDBguy said:
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.
Click to expand...
 
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 said:
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.
Click to expand...
Looking forward to it. Cheers!
 
You must log in or register to reply here.

Similar threads

M
Append Access Database with Excel Table, While Excel is open and Access closed...
Replies
5
Views
413
CJ_London
C
A
Missing data when exporting from excel to Access
Replies
11
Views
356
Isaac
Isaac
B
  • Question Question
export and transpose problem
Replies
8
Views
386
Pat Hartman
P
S
Solved Excel 2024 F8 issue in VBA Now Resolved
Replies
14
Views
434
Isaac
Isaac
Gasman
Solved Open Excel workbook, but have WorkBook_Open code run
Replies
19
Views
431
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom