MS Access macro interface with Excel question

[101287]

I'm working on an access macro that will open a directory and select all the excel files and parse them prior to load to access.

Problem is that when I read the first file everything works correct. However, when I read the second file the insert instruction does not work. Can someone help me correct this problem? I know that it is possible but I can not figure it out.

The code is below.

Sub xAlphaCharacters(W As Excel.Worksheet)
Dim rowValue As Integer, interValue As String, loopcnt As Integer
Dim raR As Excel.Range, i As Integer
Set raR = W.Cells(3, 1)
W.Columns("F:F").Selection.Insert Shift:=xlToRight

Note: It does not process the above vba statement. It says a message that I need to clear the non blanks.

Your collaboration will be appreciated.

 

[Remou]

It seems to me that it would be easier to simply append the Excel files to a table, if you intend to load them into Access.

For example:

Sub AddXL()
Dim rs As DAO.Recordset
Dim strFile As String

Set rs = CurrentDb.OpenRecordset("XLFiles")
strFile = Dir(CurrentProject.Path & "\*.xls")
Do While strFile <> ""
    rs.AddNew
    rs!FileNamePath = strFile
    rs.Update
    'Next File
    strFile = Dir
Loop

End Sub

Then there is Application.FileSearch ...