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
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.
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
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
ASKER
msgbox Src1 does return the full path. Copied and pasted in the file name.
ASKER
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.
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.
ASKER
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," PDPDaysRel easedT","C :\XE_ECM_D ays_Releas ed_PDP.xls x"
What happens?
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,"
What happens?
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?
Dump the underscores, (ex.: XEECMDaysReleasedPDP.xlsx)
Even for fun, change the extension, ( XEECMDaysReleasedPDP.xls),
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Like Rob, I think the error may be in a leading spacebar character (or other unprintable character, ...like a line break) in the FieldName
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.