Importing non-contiguous ranges from Excel

[dkmoreland]

I NEVER, EVER use ANY name that includes spaces or special characters. It is amazing the problems they cause when using VBA and queries.

There is another reference style that might work -

Set Td = db.TableDefs("[Job Info]")

I think there is no ! or . before the ( but if the above doesn't work, try adding those characters.

I know - I don't normally use spaces or special characters. I inherited this database and am having to work around stuff that was done long before I got this. It would be a nightmare to go back and change everything.

But, I do appreciate your help. I did manage to get it working, thanks to your code and the suggestion from Cronk. Now I'm just working out the kinks.

 

[Pat Hartman]

Your syntax wasn't wrong. Cronk offered a shortcut.

 

[dkmoreland]

To extract data from an area in an Excel sheet where the start and end are unknown, search down the column till the header cell is found and then continue reading cells until a blank line or other delimiter is reached.

intRow = 0  
do
   intRow=Just in case the spreadsheet is missing the header
   if intRow > 5000 then
     msgbox "Problem"
     exit do
   endif
until objXL.Cells(intRow,1) = "Materials"   '--This the name of the header in the png supplied by the OP

intRow = intRow + 1     '--skip the next line has the column headings

While objXl.Cells(intRow,1) <>""
   rs!Field1= objXL.Cells(intRow,1)
   rs!Field2= objXL.Cells(intRow,2)
   rs!Field3= objXL.Cells(intRow,3)
   .....
   intRow=inRow+1
loop

I have additional questions about extracting data from Excel when the start and end cell address are unknown.

Please see the highlight part of the attached screenshot. I need to make a comma separated list of those bulk ids and put them all in a field in the table. There are not always 3 of them - sometimes there is only 1 and sometimes there are several. Do I need to use an array or a collection to make a list of these cell values?

I also need to add together the values in the shot size field for each of these bulk lines. For example, since the attached has 3 lines with a shot size of 200, I need to set the access field value to 600. If there were 7 lines with a shot size of 200, the value would be 1400. Does that make sense?

So, how can I adapt the code shown above to create a list from a unknown number of cells and to add together the values from another unknown number of cells?

I haven't used arrays in a long time and am definitely rusty. And, I am quite aware that there may be a better way than an array to create a list.

Thanks so much for the help.

 

[dkmoreland]

Does anybody have any advice on my last post or should I start a new thread?

Thanks,
Diana

 

[Cronk]

Is the output you want for the 3 line sample you provided
bulk1, bulk2, bulk3 in one field and 600 in the second?

If so, I would not be using arrays. Just concatenate the ID values and add the shot sizes as the group of cells is read one at a time.

 

[dkmoreland]

Is the output you want for the 3 line sample you provided
bulk1, bulk2, bulk3 in one field and 600 in the second?

If so, I would not be using arrays. Just concatenate the ID values and add the shot sizes as the group of cells is read one at a time.

Yes - that is the correct output. I just posted a new thread on this that has the code. The question is what to do if I don't know how many values there will be to concatenate or add together.

Here's the link to the new thread:

https://www.access-programmers.co.uk/forums/showthread.php?t=298621