Loop until last record in excel table

[compooper]

Hi folks,

Please look at the following code. I am trying to write data from excel until the last record in the table, including any in between blanks.
This however stops at the first blank cell in the column.

I have been trying to use the EOF method, but I'm not understand the syntax. I've also tried going until record 65536, but that just slows down my database.
Any help is greatly appreciated

Option Compare Database

Function IsExcelRunning() As Boolean
    Dim xlApp As Excel.Application
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    IsExcelRunning = (Err.Number = 0)
    Set xlApp = Nothing
    Err.Clear
End Function

Function funExportBatch(strFilePath As String, strWorksheet As String)

Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim ExcelRunning As Boolean

ExcelRunning = IsExcelRunning()

If ExcelRunning Then
    Set xlApp = GetObject(, "Excel.Application")
Else
    Set xlApp = CreateObject("Excel.Application")
End If

' Replace QueryOrTableName with the real name of the table or query
' that is to receive the data from the worksheet
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("TestTable", dbOpenDynaset, dbAppendOnly)

Set xlBook = xlApp.Workbooks.Open(strFilePath)
Set xlSheet = xlApp.Worksheets(strWorksheet)
Set xlstart = xlSheet.Range("A2")

r = 3 ' the start row in the worksheet

Do While Len(xlSheet.Range("A" & CStr(r)).Formula) > 0
    ' repeat until first empty cell in column A
        With rst.AddNew ' create a new record
            ' add values to each field in the record
            rst.Fields(1) = xlSheet.Range("A" & r).value
            ' add more fields if necessary...
            rst.Update ' stores the new record
        End With
        r = r + 1 ' next row
Loop
    rst.Close
    Set rs = Nothing
    dbs.Close
    Set dbs = Nothing
    

' Close the EXCEL file without saving the file, and clean up the EXCEL objects
Set xlstart = Nothing
Set xlSheet = Nothing
xlBook.Close False
Set xlBook = Nothing
Set xlApp = Nothing

End Function

[June7]

I am afraid you either have to know the final row number or use a column that will have a value in every row. Why are there blank cells? Could you sort the rows so there would be no blanks?

An alternative might be to link to the spreadsheet then work with it like a table

[compooper]

Hi June,
Thanks for replying.
Unfortunately I can't change the set up of the excel file.
The blank cells are there since the company who created it, decided it would be easier to read if clientName appears once and all of their corresponding services underneath.
So its like this:
Row 1: Company Header
Row 2: Case #/Date, Agent/Agency, Applicant/Notes, Policy #/DOB, City, Province, Svc, Description, Completed, Fee, Total,
Row 3: #, Text, (Text, Text), Text, Text, Text, Text, Date, $, $
Row 4: Date, Text, Null, Date, Null, Text, Text, Date, $, Null
Row 5: Null, Null, Null, Null, Null, Text,Text,Date,$, Null
Row 6: Null, Null, Null, Null, Null, Text, Text, Date, $, Null
I know it sucks. But thats how it is for each client, and it can run as long as how many services they ordered/completed.

If I were to link the Excel File, would this slow down my system?
Could you please show me an example of linking & writing from an excel file.
Thanks

[June7]

Try this.
Use one of the data columns (Province, Svc, Description, Completed, Fee) that always has a value except on the Company Header Row. Then in code test if there is value in cell and is not column title. Depending on what is found, grab data or move to next row. If no value, check for value in cell A__ for the Company Header. If found then move to next row and continue. If not then must be end of data.

I am confused by the number of column titles is eleven but only 10 data items. And Row 3 throws me off. The point is, as long as there is a consistent pattern, can code for it.

Link to an Excel workbook/sheet with the External Data dialog. I don't think linking will slow down processes. I have linked to workbook only once and it was not a large file. But problems with this worksheet is row 1 does not have column titles and the repetition of rows like 1,2,and 3 will be an issue because of datatype. Everything would have to be treated as a text datatype, including the currency and dates values. You can't change how the worksheet is given to you but you can change it - manually or with code.

[compooper]

Hi June,

When you delete records/change things in a linked table, does that alter the original table saved in the computer?

I like your idea about the finding the last cell.
If you look at the attached picture, it might give you a better idea about the table I'm working with.

I would have to run a delete query first to get rid of all of the blank records, and then search for my last field.
I was also thinking to repeat several of the data, like ClientName for the entire order in order to have less blank fields.
Once I merge all the data I need to my existing table, I can (using my relationships) clean up the data by name, policy# etc.

When you link files, can you delete specific rows/records? Idealy I would want to get rid of the company header, and all blank fields.

[June7]

I am not sure can delete rows of linked spreadsheet. Not even sure cells can be edited. haven't tested those. The one link to spreadsheet I have I only read the data, don't edit through the link. I push mass edits to the spreadsheet by VBA code.

But yes, can alter source data through links, such as links to tables in another Access file.

This worksheet structure is awkward and don't think it is suitable for linking as is because of the dual data use of 3 columns. But could be handled by code. The code would test for value in Svc column, if none then test for value in next row. If value found you know you are on first row of new grouping, if none then no more data.

Initiate r = 2 then in the loop immediately increment by 1 so the first tested row is row 3.

If the sheet is only for one company and the company name is only at the top, this is good.