Run VBA from within Access on Excel workbook range

I am importing data from Excel into Access.  The Excel data is slightly corrupted.  If the below code is run from within Excel over the data about to be imported into Access, Access imports the data with no problems.

What I want to be able to do is run the code from Access on any workbook and named range the user enters.  This will give me the ability to fix the corrupted data right from Access with out going into Excel first.

Any help on this?  See code below that is currently run from within Excel.


Sub MassageImportData()
    Do While IsEmpty(ActiveCell.Value) = False
        If IsEmpty(ActiveCell.Value) = False Then
            Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                :=Array(1, 1), TrailingMinusNumbers:=True
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Offset(0, 1).Activate
        End If
End Sub

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Well you will  have to rewrite this function. It needs Parameter, the workbook name e.g and the range. You then instantiate on the Access side Excel and call this function. I'd place this function in it's own Module.

I guess the following books would help you:

Since this is done from Access, you should start with:

Dim Xl As Excel.Application
Dim Wb As Excel.Workbook
Dim Wsh As Excel.Worksheet

you will then need to open a spreadsheet

Set Wb = XL.Workbooks.Open (......)
Set Wsh = Wb.Worksheets(....)

Then identify the range holding data
Then loop through that range column by column, or apply the changes to teh entire range.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.