Can 'DoCmd.TransferSpreadsheet' do this?

travismallen · March 14th, 2007, 02:04 PM

Hi All,

I've only seen simple examples of 'DoCmd.TransferSpreadsheet', so I'm not sure if it can do want I'm trying to accomplish.

I have a directory of Excel workbooks, each with two sheets I need data from. One has the majority of the data I need (columns and columns of data) For the other, I just need the creators name and region from two cells.

I want to bring the main data into a table and then update the table last to fields with the name and region of the owner.

I've never used 'DoCmd.TransferSpreadsheet' and I haven't seen good examples how to use the range property to grab what I want. I figure sorting that out will get me the majority of my data. It's not working, but here's what I have:

Code:

Public Sub PopTb()
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "InfoSecTempTb", "I:\Planning and Technology\RCSA Analyzer\*.lxs", False, a3: ai46 , "Matrix-InfoSec$"
End Sub

For the other two fields I was thinking there must be a way to assign each one to a variable and run an update query, but then I'd have to run that update after every sheet import.

Is there a better way to do this? I thought maybe with ADOD functions, but I can't find anything on it. You ideas would be greatly appreciated.

Kind Regards, Travis

mmcdonal · March 15th, 2007, 06:49 AM

Pull the other field range in as a new table, then take the values from the local table instead of the spreadsheet. You can create queries, or use ADO/DAO to run updates on the first table here.

Running the downstream queries or functions should not add much processing time.

Does that help any? Do you need more help with this?

mmcdonal

travismallen · March 15th, 2007, 10:13 PM

This helps, I just have one more question. My attached code doesn't seem to work. Can you tell me what's wrong with the syntax. I'm thinking it doesn't like the wildcard, but if you would happen to know it would save alot of guess and checking! Thanks for your help so far. T

martinrhague · March 16th, 2007, 04:07 AM

I have had a look at your code and it looks like you have the X & l the wrong way round. I presume you are looking at Excel sheets. should be xls not lxs

HTH

mmcdonal · March 16th, 2007, 06:33 AM

Yeah, what he said.

mmcdonal

travismallen · March 16th, 2007, 05:31 PM

*Argh* Stupid typo! Thanks guys, you rock!