simple loop through excel cells (1 Viewer)

[vman21]

Hi guys,

I have been trying to understand which method to use for looping through excel cells and storing these into access tables. However, I am having difficulty with so little knowledge in vba.

I have lets say two tables (rows for each table are not fixed) in a worksheet and I want to loop through these rows and store each tables (PROJECT PLAN 1 and PROJECT PLAN 2) in a separate table in access.

PROBLEM: I don't know how to loop through the PROJECT PLAN 1 and PROJECT PLAN 2 in excel and store these in table1 and table2 in access

I have these in excel

B3 I have "PROJECT PLAN 1"
B4 COMPANY | C4 DESCRIPTION | D4 TIME
B5 Google | C5 aaa | D5 10
B6 Microsoft | C6 bbb | D6 11
B7 IBM | C7 ccc | D7 12

next row 8 is blank row and columns

B9 I have "PROJECT PLAN 2"
B10 COMPANY | C10 DESCRIPTION | D10 TIME
B11 Google | C11 aaa | D11 10
B12 Microsoft | C12 bbb | D12 11
B13 B14(merge cell) IBM | C13 ccc | D13 12
B13 B14(merge cell) IBM | C14 ccc | D14 12


This is the code I have so far:

Dim objXL As Object
Dim xlSht As Object
Dim xlWB As Object
 
Set objXL = CreateObject("Excel.Application")
Set xlWB = objXL.Workbooks.Open(txtExcelFile.Value) 'assuming txtExcelFile.Value is aa.xlsx
Set xlSht = xlWB.Worksheets(cboExcelFile.Value) 'assuming cboExcelFile.Value is the worksheet name Sheet1
 
For Each cell In xlSht.UsedRange.Cells
      If cell.Value = "PROJECT PLAN 1" Then
              'next row as column headers will be imported to access table1
              'follwing row as values
              'stops if an emplty cell is found (for example b8)
 
      Elseif cell.Value = "PROJECT PLAN 2" Then     
              'next row as column headers will be imported to access table2
              'follwing row as values
      End If
Next

Thanks in advance.

 

[pr2-eugin]

vman 21, Welcome to AWF

You have shown the Data you have, the code you have. Very good, but you have failed to say what is the problem you are having. Could you give another shot?

 

[Frothingslosh]

Could you perhaps create a named range that includes each table? Once you do that, you can use DoCmd.TransferSpreadsheet to import the data quickly. (A named range isn't necessary; it just helps. You can also use expclicitly designated ranges.)

 

[vman21]

Thanks for the warm welcome Paul.

The problem is I don't know how to loop through the PROJECT PLAN 1 and PROJECT PLAN 2 in excel and store these in table1 and table2 in access. I have seen numerous examples but are too complicated to understand.

I am trying to use this code I found below but not sure how Range should be declared?

[B][SIZE=2]To Search a Dynamic List or a List with an Unknown Number of Rows[/SIZE][/B]

This code moves down column A to the end of the list. 
(This code assumes that each cell in column A contains an entry until the end.) 
 
Sub Test2()
' Select cell A2, *first line of data*.
Range("A2").Select
' Set Do loop to stop when an empty cell is reached.
Do Until IsEmpty(ActiveCell)
' Insert your code here.
' Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select
Loop
End Sub

 

[vman21]

Hi Guys, I finally was able to make this work, however, I have a question. In excel I have a cell containing a paragraph with carriage returns or multiple lines and I want to keep this format in access table. What data type should I use in access and how should I pass this value into the array?


Anyway, here's the code below for anyone who needs it!

Dim objXL As Object
Dim xlSht As Object
Dim xlWB As Object
Dim xlRange As Object
 
Set objXL = CreateObject("Excel.Application")
Set xlWB = objXL.Workbooks.Open(txtExcelFile.Value) 'assuming txtExcelFile.Value is aa.xlsx
Set xlSht = xlWB.Worksheets(cboExcelFile.Value) 'assuming cboExcelFile.Value is the worksheet name Sheet1
 
xlSht.Activate
 
For Each cell In xlSht.UsedRange.Cells
     If cell.Value = "PROJECT PLAN 1" Then
 
     startRow = cell.Row 'row index number of the title (excel table)
     startCol = cell.Column 'column index number of the title (excel table)
 
     Set xlRange = xlSht.Cells(startRow + 1, startCol).CurrentRegion 'selects the active region (PROJECT PLAN 1 table)
     ReDim strArray(0 to 2) As String
          For r = startRow + 2 To ((startRow + 1 + xlRange.Rows.Count) - 2)
          i = 0
          For c = startCol to (xlRange.Columns.Count + 1)
               strArray(i) = xlSht.Cells(r, c)
               i = i + 1
               If c = xlRange.Columns.Count + 1 Then
                    DoCmd.SetWarnings False
                    company = strArray(0)
                    description = strArray(1)
                    time = strArray(2)
 
                    SQL = "INSERT INTO PROJET_PLAN_1 ([company],[description], [time]) VALUES ('" & company & "', '" & description & "', '" & time & "');"
                    DoCmd.RunSQL SQL
                    Erase strArray
                    ReDim strArray(0 to 2)
                End If
            Next c
        Next r
 
        xlWB.Close
        objXL.Quit
 
Elseif cell.Value = "PROJECT PLAN 2" Then 
     'same as above block of codes but may need some changes depending on your table size
End If
Next

By the way, I was typing this reply and got away for a few minutes and guess what I was logged out, and I now had to retype everything again.

EDIT: and also I was getting an error when I do
Dim xlRange As Range or Dim objXL as Excel.Application, where the rest of the samples from the internet they do that with no issues?

 

[Mihail]

Maybe this can help you:
http://bytes.com/topic/access/answe...fully-customized-export-data-access-excel/new

 

[Mihail]

And this:
Recordsets for Beginners - Access wiki - Access Help and How-to - Microsoft Office by UtterAccess.com