Two identical import procedures; one runs, one doesn't.

The only difference in the procedures is the file name. Public Sub LinearityData() runs; Public Sub PDPDaysReleased() does not. Throws Error 3125 (" is not a valid name).  I'm stuck; can't see the difference.
Public Sub LinearityData()

    Dim sPath As String, Src1 As String, Irc1 As String
        sPath = "\\fhxnas02\pdcteams\combine\AgPDM Reports\0-Daily\"
        Src1 = sPath & "XE_HX_CP_Linearity_Summary.xlsm"
        DoCmd.DeleteObject acTable, "LinearityT"
        DoCmd.TransferSpreadsheet acImport, 10, "LinearityT", Src1, True, "Data!A6:CK200000"
        DoCmd.DeleteObject acTable, "Data$A6:CK200000_ImportErrors"
    
End Sub

Public Sub PDPDaysReleased()

    Dim sPath As String, Src1 As String, Irc1 As String
        sPath = "\\fhxnas02\pdcteams\combine\AgPDM Reports\0-Daily\"
        Src1 = sPath & "XE_ECM_Days_Released_PDP.xlsx"
        DoCmd.DeleteObject acTable, "PDPDaysReleasedT"
        DoCmd.TransferSpreadsheet acImport, 10, "PDPDaysReleasedT", Src1, True, "Data!A5: BT100000"
        DoCmd.DeleteObject acTable, "Data!A5: BT100000_ImportErrors"
    
End Sub

Open in new window

Jay WilliamsOwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
First off, please remember to tell us which line is giving grief. Makes it much easier.

Im not 100% sure as to the issue, but a couple of things springs to mind. In one case you are opening a xlsm file, in the other case its a xlsx file. But both use the 10 as the value for SpreadSheetType for Transferspreadsheet
There is also a space in "Data!A5: BT100000", but I don't know if it makes any difference at all.
hieloCommented:
On lines 18 and 19, you have a leading space to the left of "BT100000".  This space is not on the working code.

On line 19, you have a "Data!", but on the working code you have "Data$".

See if those changes make a difference.
Jeffrey CoachmanMIS LiasonCommented:
Make sue that  "Src1" returns a valid Full Path (Drive, Path, FileName, Extension)

    msgbox Src1

Make sure that if the Path looks valid that those attributes actually exist in the specified location.
Finally, check the spelling carefully.

JeffCoachman
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Jay WilliamsOwnerAuthor Commented:
msgbox Src1 does return the full path.  Copied and pasted in the file name.
Jay WilliamsOwnerAuthor Commented:
Thanks hielo, I did what you said, but no difference.
Robert ShermanOwnerCommented:
(EDIT: Just to echo what the first response said, stepping through in the debugger to find out exactly which line number is causing the error would also be helpful info.   Should the below suggestion not work...)

Since you have the paramater for "HasFieldNames" set to True, the import is trying to use the first row in the spreadsheet range to set the names of the fields upon import.

In the spreadsheet for PDPDaysReleased, you likely have an entry in the first row which Access doesn't consider a valid field name.    Take a look at what's in the first row for each column, and look for things that might not be valid, such as certain punctuation (eg semi-colons).   Alternatively, set the value to false as a test to see if the import runs without setting the field names based on first row.    You'd still need to go through them to find out which one is causing the error, but if there's a lot of columns this would be a faster test before you go through each column looking for invalid names.
Jay WilliamsOwnerAuthor Commented:
Ok, I'm back working on this now; have tried every strategy listed and there is still something else wrong.  I'm going to look at some workarounds,
Jeffrey CoachmanMIS LiasonCommented:
You still have not stated what line is throwing the error...?

Also, please remember to always test a "simple" version of your code...
If that fails, then why add things like compound variables or other function arguments...

For example, make a copy of the Excel file and put it in c:\ and run code like this:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,"PDPDaysReleasedT","C:\XE_ECM_Days_Released_PDP.xlsx"

What happens?
Jay WilliamsOwnerAuthor Commented:
It is the TransferSpreadsheet line that fails; file saved in every locations renders error:
error 3125
Jeffrey CoachmanMIS LiasonCommented:
So shorten the name,.. (ex.: text.xlsx) , ...what happens?

Dump the underscores, (ex.: XEECMDaysReleasedPDP.xlsx) ...what happens?

Even for fun, change the extension, ( XEECMDaysReleasedPDP.xls), ...what happens?
Jeffrey CoachmanMIS LiasonCommented:
Also check the ImportError table, for anything suspicious.

Finally, make sure that this is not an invalid character *Inside the spreadsheet*
So again, keep it simple, make a test spreadsheet with just a few rows of data with the same headings, ....what happens?

How about posting a sample of this spreadsheet for us to examine?
Robert ShermanOwnerCommented:
As I said above, I believe what you're hitting is an error caused by an invalid FIELD NAME character in headings row of your 2nd spreadsheet.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jay WilliamsOwnerAuthor Commented:
Thanks guys. I'm not ignoring you, bosses are just temporarily redirecting my attention.  I get the fieldnames argument and have run numerous tests on it with other files. Next I'll work on the file name angle. The link to the file is on a SharePoint site, but the file is actually in a network location.  It could be my permissions aren't there.  Maybe I'll have a chance to look at it Thursday.
Jeffrey CoachmanMIS LiasonCommented:
Again, can you post an example file for us to evaluate?
Like Rob, I think the error may be in a leading spacebar character (or other unprintable character, ...like a line break) in the FieldName
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.