Link to home
Avatar of Jay Williams
Jay Williams

asked on

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

Avatar of Anders Ebro (Microsoft MVP)
Anders Ebro (Microsoft MVP)
Flag of Denmark image

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.
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.
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
Avatar of Jay Williams
Jay Williams

ASKER

msgbox Src1 does return the full path.  Copied and pasted in the file name.
Thanks hielo, I did what you said, but no difference.
(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.
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,
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?
It is the TransferSpreadsheet line that fails; file saved in every locations renders error:
User generated image
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?
SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
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