Combining Macros

[CalvT]

Because Access doesn't support text to column, I've created a linked workbook. Now what I want is that when the workbook is opened, a macro is run automatically, then the workbook is closed and saved, also automatically.
The macros I have found are:

To run the macros

[COLOR=#333333]Private Sub Workbook_Open()[/COLOR]
[COLOR=#333333]Enter your macro here[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

To split the text

Sub CopyAndSplit_IDEC1()  
Dim Words As Variant
  Words = Split(Sheets("Sheet2").Range("A1").Value)
  With Sheets("Sheet1").Range("A1").Resize(, UBound(Words) + 1)
    .Value = Words
    .Value = .Value
  End With
End Sub

To close the workbook

Private Sub CommandButton1_Click()    
   Application.DisplayAlerts = False
    With ThisWorkbook
        .Save
        .Close
        End With
    Application.DisplayAlerts = True
End Sub

Now I have two problems. One is that the macro to split the text only does one cell, I want it to do all the column if possible. The values are numbers separated by spaces. And the other problem is: how do I join them into one macro? Sorry to be so thick!
Thanks

 

[jsotola]

why don't you use the split function in Access and avoid using excel altogether ?

Split Function - Access - Office.com


you just have to add code to loop through all the array elements and copy each element into a separate recordField