DoCmd.TransferSpreadsheet acImport (multiple worksheets) fail

[Larryg]

Hello All;


I'm having an issue IMPORTING a specific Worksheet within an Excel Workbook. The current error (multiple errors encountered in numerous attempts to cure the fail) is "Field F1 does not exist in destination table "tbl_MyAccessTableName".
Details:
Destination table = "tbl_MyAccessTableName"
WorkBook name to open = "MyWorkBookName_withCurrentDate.xls"
WorkSheet Name = "My WorkSheetName" (yes, there is a space in the worksheet name, and yes there are multiple worksheets in this workbook)

Problematic issues: WorkSheet has column headings in row 1 (columns A through F (which remain constant)), however row range (number of records) varies each day (new file each day).
Destination table has different field names than that of the source worksheet, and has spaces in those source field names. Also, I want to APPEND the imported data to the existing data in the table. Destination table also has additional fields not being appended to.

Code currently being tried, which produces the above reference error:

strTable2=tbl_MyAccessTableName
strFile=Dir(strPath & "*.xls")

(The code prior to the DoCmd line navigates to the file location, and allows selection of the desired workbook file, and works as intended.)

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable2, strFile, True, , Range = "[My WorkSheetName]$A2:F500"
'(using 500 in an attempt to over estimate possible number of records)

Removing the 2nd comma after "True" produces the error: Runtime error 3011, "The Microsoft Engine could not file the object "0", bla bla bla.

I have tried so many iterations of the "DoCmd" line that I have lost track. Also tried substituting the "$" with the "!", no joy.

Any help would be greatly appreciated.

Larry

[Micron]

The only suggestions I have at present is to replace this part Range = "[My WorkSheetName]$A2:F500" with this: "A2:F500"
or, if it is the same spreadsheet every time with different data, why not just link to it as a table and run an append query against it into your table? To prevent duplicating data, make whatever fields you need to be as unique in the target table and trap the error message or turn off warnings during the update.
You can also define a dynamic range in Excel and reference the range by name rather than use 500 rows.

[Larryg]

Micron, thanks for the reply. I don't think linking is an option as the file is created (by someone else) and stored in a shared location with the then current date appended to the standard file name (i.e. MyFile_05282016). So, each daily file is in fact a different file name, with different data (rolling data of open jobs in varying stages of completion).

Would it be possible to have VBA code to navigate to the file, change the name, then link/append as you suggest? I'm an intermediate VBA coder at best, so that is above my skill set, so just asking.

Thanks...

[Micron]

Are you sure it makes sense to navigate to a different file each time by using vba when you can simply use the Get External Data function built into Access? The path you use would be saved by the import & link action by the user, up to the folder level. Otherwise, yes, you'd use the MSOFileDialogFilePicker to locate the file and return a reference to your code - https://msdn.microsoft.com/en-us/lib.../ff196794.aspx and https://msdn.microsoft.com/en-us/lib.../ff196794.aspx
From what you've described, I would not be using this method to change a file name to something that probably already exists and is different. You could use the method to pick the file and pass its name to code whereby you use the TransferSpreadsheet method. However, you ought to consider the suggestion regarding the range reference. Yours doesn't look right.

[Larryg]

Micron... I was trying to avoid using the Get External Data function because I feel too many things can go wrong having the end user do more than push a button entitled "Get New Records" or something similar. And yes, what I am currently trying is the MSOFileDialogFilePicker comcept similar to the code you referenced in the links you provided. My code thus far navigates to the file properly, but once "picked", the code can not locate the correct Worksheet within the desired Workbook (file) selected. I haven't tried shortening the Range part yet, so I don't know if that will be successful. Seems to me I have tried that in my epic number of iterations attempted thus far, but will certainly try it just in case. I will continue to search for solutions but find few references to selecting specific worksheets within a workbook, at least any that worked for me so far.

Thanks...

Larry

[Micron]

You can always post a stripped down, zipped copy of your db (and maybe one of your Excel file) for us to play with. Note that if features not supported by Access 2007 are used, I won't be able to open it.

[Larryg]

Micron... I utilized the code at www.mvps.org/access/api/api0001.htm and it seems to be selecting the desired Excel file, and it attempts to (I think) load the correct worksheet. However, since the field names are different in the destination table (I did not duplicate the use of spaces in the Access table field names), it is throwing an error that the destination table does not contain the field "My FieldName" (i.e a name with spaces in it). I guess I could rename the destination field names to match the ones in the source file, but wanted to avoid file names in my Access table that has spaces in them. Any thoughts ???

Thanks...

Larry

[ssanfu]

This is a great question!

I have now learned I can open an Excel workbook and get data from a specific worksheet using a SQL statement.
(http://www.accessmvp.com/kdsnell/EXC...ort.htm#ImpSQL)
This creates a recordset which I can then loop through to process. Might be faster than saving the Excel worksheet as a CSV file, then importing the CSV and processing it.

Hopefully Micron can get you on the tight track. If not, post back and maybe the VBA/SQL option will help.......

[Larryg]

ssanfu... Sounds promising for later use, but my spreadsheet file name changes daily, and, contains multiple worksheets (of which I need only one).

I think I have the issue solved except for the file name (having spaces) issue (post #7). The code is grabbing the correct worksheet, and although I have not actually imported the data yet, I am fairly confident if I change the table names to match the spreadsheet names, it will import just fine. I guess I can work with the spaces, but would like to have a solution to be able to utilize the table names void of spaces. I'm initially thinking a temp-transfer table type of work-around. Hopefully I can figure out a way to get all the steps under 1-button on the form.

Thanks for the reply...

Larry

[Micron]

You've answered every concern, I think, except for whether or not you changed the Range reference per post #2 and #4.
True, , Range = "[My WorkSheetName]$A2:F500" versus True, , "$A2:F500"
I am not an Access Version History expert by any means, but it appears you chose an overly complicated API call from 1997 over the common file dialog. AFAIK, it's far more than what you need and the task was probably simplified long ago when the common file dialog was made available in Access. What about the two links I gave you on using that? It's much simpler.

I'm also re-reading all to figure out where csv file came in to the picture and I can't. Sounds like you are trying to include the worksheet column headers in the transfer, which would give you the error (because of your parameters in the transfer statement), especially if it's from a csv file. But you said it was a spreadsheet?? I considered that in the beginning, but noted you start at A2, and set the 'has headers' parameter to True, which should start in the correct row - unless you have merged rows in your header columns. Which brings me to the last concern I have about not knowing which suggestions you have tried or not, or haven't addressed - posting a db and spreadsheet. IMHO, this is not a real simple problem to trouble shoot given all that could be wrong, from the spreadsheet itself to the Access code, which we haven't seen much of either. A look-see into the guts of what you have would help a lot.

Edit: I haven't gleaned enough from what you have to know if the spaces are an issue or not. I've never encountered this (maybe because I avoid them like the plague), and since Access is really not all that demanding (if it was, it would not allow some of the names we see here to be used) it's difficult for me to accept this is the problem. However, if it turned out to be true, then alias names may help. I fear you are off track though, because of the syntax I bolded has yet to be proven/disproved as well as the idea of merged cells, as well as the code...

[Larryg]

Micron... in short, I ended up using: ... Range = [My WorkSheetName$] I'll post the exact line in a later post (see below)

Yes, I agree, using the mvps.org code was overkill, however this is still a work in progress. I will be going back to clean up any unnecessary code once I have the code working the way I want it to. I guess like most perhaps, my primary concern is to get the code working, and for the development phase, the mvps code got me to where I needed to be in order to proceed.

Just to clear up some confusion on post #8/9, the Excel file I am working with is not .csv, it is a simple .xls file/workbook/worksheet. You are right, I had not referenced anything related to csv, but I thank ssnafu just the same.

Additionally, this is a work effort, and being a holiday weekend I was doing some work at home. My company has a Chinese Wall between its workers and the outside world, therefore I am limited to what I can post, hence the alias field headings and general vagueness. I can not copy and paste any code because the work-at-home connection software does not allow any interaction between my home system and my work pc, so its memory and manual typing that rules the day.

And finally, yes, the spaces in the field headings of the desired worksheet are still an issue as I see it. When I change the field headings of the table to mirror that of the worksheet (i.e. spaces included), there are no issues apparent at this time. Today I will make a copy of the table, change all the field heading to mirror the worksheet, and process the full import of the data. As I mentioned, I am confident the full import will be successful (based on testing thus far) , and then I will work on overcoming the spaces issue.

Thanks so much for your interest in helping, the effort is certainly appreciated.

Larry

[Micron]

When I change the field headings of the table to mirror that of the worksheet (i.e. spaces included), there are no issues apparent at this time

If changing them is possible and fixes your problem, then that would be the course to take. I haven't read anything yet that tells me that the issue is not because of the spreadsheet design that you are picking up the column names when you have set the transfer parameters to not do so. Changing the names would negate this if it's the problem.
Good luck!

[ssanfu]

Just to clear up some confusion on post #8/9, the Excel file I am working with is not .csv,

Sorry to create confusion.

The CSV file was just a comment of what I was having to do because a client was mangling.... uh... sending the data with sooo many errors.
Probably shouldn't have even mentioned it.......

Larryg's question did help me understand that an Excel workbook could be open and data retrieved without using Automation code and without linking.