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?
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.
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:

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.