transferspreadsheet range

[monkeytunes]

Hey buddays,

We recently sent out a questionnaire/suggestion list to a bunch of employees (several hundred) and are expecting responses soon. The questionnaire is in Excel format with two worksheets. The first sheet is locked/protected and only contains instructions for users. The second sheet is the questionnaire, and is locked/protected except for the designated fill-in-the-blanks: the usable range starts with row A6:F6, and users can fill in as many suggestions as they want. (The top ranges are taken up by company logo, field names, and two example inputs, all locked.)

I'm using one of the import database provided by IMO in this Excel-to-Access thread, which I've modified slightly for my usage. My problem is with defining those ranges mentioned above.

Currently I'm doing this:

DoCmd.TransferSpreadsheet acImport, , tblName, InputDir & ImportFile, True, "Sheet2!A5:F150"

("tblName", "Input Dir" and "ImportFile" are defined within the aforementioned database from IMO.)

The problem with naming the range A5:F150 is that I get tons of empty records - what if a user only puts in 2 suggestions? And what if a user puts in 155 entres? I'll lose data. How can I keep out null values while ensuring that I'm not losing data? Ideally, I'd go "Sheet2!A:F", but like I said, I have those top rows reserved, and the real data doesn't start until row 6. Is there a validation function or a way to prevent importing of blank rows?

Any ideas?

 

[boblarson]

This may be out of your abilities but you could open the Excel sheet with code and check it to see what the valid range is and then dynamically change your range.

Or, since you would have the Excel sheet open, it could just pull the data in via a DAO or ADO recordset.

Each of these would take some coding to do, so I don't know how comfortable you are with that. I don't have time to do up an example, but I have done it this way (the second suggestion) in the past.

 

[sportsguy]

DoCmd.TransferSpreadsheet, acLink. . . . .

DoCmd.RunSql "Insert into. . . . Select . . . . where Keyfield IS NOT NULL;"


attach the spreadsheet as a linked table,
use an append query, with a criteria of no blank rows. . .

makes sense. . right?

sportsguy

 

[monkeytunes]

boblarson,

I'm not terribly adept at coding. I can walk-through others' examples and modify it for my needs, but I'm usually unable to do complex operations without an example to work from. Sounds like a good idea, but I'm curious as to what "dynamically change your range" means - we're potentially getting hundreds of copies of this spreadsheet survey back!


Sportsguy,

Would I have to link each spreadsheet? Do you have an example of this in action? This survey was sent out to over 800 people!

Thanks for your replies!

 

[sportsguy]

yes, if you search all my posts, and use the search term sportsguy,
the code is posted here.

I designed an excel to access forecasting database that consolidated almost 200 hundred spreadsheets into access and the import OR UPDATE took less than five minutes total. its the monthly forecast for almost 200 districts of a $2 billion company. . . .

you can do it.

sportsguy