Sort/Manipulate Excel data from Access 2003 VBA

Question

I'm trying to run a module in Access 2003 that takes an excel file, if there is info in the cell, then copy the field header and cell in A1, B1, etc... as it loops through each row. There are also comments in the cells with data that need to be copied.

So in a perfect scenario the code would go through each row, cell by cell, and grab the user, the date, the cell's data and the comment and create a new record in an already existing access table. If the cell is empty then it will be bypassed. I have 4 sheets and I know how to make code loop, I just can't get access to pull from excel at all.

I don't want to just import data either, it needs to be sorted with comments in tact.

Any advice would be greatly appreciated. Thanks!

Below is the closest I think I've gotten btw:

Sub copy3()

Dim rs2 As New ADODB.Recordset
Dim cnn2 As New ADODB.Connection
Dim cmd2 As New ADODB.Command

With cnn2
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\FilePath.xls;" & "Extended Properties=Excel 8.0;"
.Open
End With

Set cmd2.ActiveConnection = cnn2
cmd2.CommandType = adCmdText
cmd2.CommandText = "SELECT * FROM [Jan-Mar$] Where [Name] IS NOT NULL"
rs2.Open cmd2

While Not rs2.EOF
...Not sure what to write here but I got a msgbox to appear...
rs2.MoveNext
Wend

Something like ? http://stackoverflow.com/questions/14285106/formatting-tables-imported-via-a-report/14285559#14285559 — Fionnuala, Jan 21 '13 at 22:50
This is very close to what I'm looking for, yes. But instead of getting it to move row by row, I'm trying to get it to go cell by cell and pick out the cells with data. Does .Rows.Count do this? I'm reading about it but only understanding part of it... And what does 'If rng.Cells(i, 1) Like "Program*" Then' do? I'm familiar with If Then statements but I've never used Like. Thanks — Grant, Jan 23 '13 at 19:20
Nevermind, I figured out Like. I just needed to mess with it more. But for moving cell by cell to check if null or contains value, should I use an integer that resets to 1 after reaching the end of each row? — Grant, Jan 23 '13 at 19:43
`For i = 1 To rng.Rows.Count` is for each row *i* in the range `rng`. You can get the cells from the row : `For i = 1 To rng.Rows.Count For j = 1 To rng.Rows(i).Cells.Count Debug.Print rng(i, j) Next Next` — Fionnuala, Jan 23 '13 at 19:44

score 2 · Accepted Answer · answered Jan 23 '13 at 23:31

Thanks Remou for the advice and help. This is what finally worked for me:

Set rs = db.OpenRecordset("TestTable")
xl.Visible = False

Set ws = xl.Workbooks.Open("C:\file.xls").Sheets("Jan-Mar")
Set rng = ws.UsedRange

'Row 1 is all dates so start at Row 2
For i = 2 To rng.Rows.Count
    'Column A contains names so start at Column B
    For j = 2 To rng.Rows(i).Cells.Count
        If rng.Cells(i, j) = "" Or rng.Cells(1, j) = "Total" Then

    Else
        On Error Resume Next
        rs.AddNew
        rs!EmployeeName = rng.Cells(i, 1)
        rs!Date = rng.Cells(1, j)
        rs!PointValue = rng.Cells(i, j)
        str = rng.Cells(i, j).Comment.Text
        str = Right(str, Len(str) - 14)
        'MsgBox str
        rs!Comments = str
        rs.Update
    End If
Next
Next
Set rs = Nothing
Set rng = Nothing
ws.Parent.Close
xl.Quit

Sort/Manipulate Excel data from Access 2003 VBA

1 Answers1