transferspreadsheet not functioning.

[fb63]

My code to transfer spreadsheets is picking up a invalid range somewhere. If I only import the ME file, I get all 52 fields, if I import both, in any order, I only get the first 32 fields of both files, as the EXP file only has 32 fields.

The excel files are being deownloaded from peoplesoft everyday with no changes to fields. I can't find any invalid data, which I don't think would be the issues since it will load correctly if I reset the flag on that file and rerun the code.

'**************************************START CODE**************************************
Public Function LoadPeopleSoftData()

'declare variable
Dim rs As ADODB.Recordset
'set variable equal to empty recordset
Set rs = New ADODB.Recordset
' set up variable to put the query statement into
Dim strSQL As String
' set variable equal to the query
strSQL = "SELECT * FROM tbl_PS_Files;"
' set variable to use for Match Exceptions
Dim stMEFile As String
' set variable to use for Expedites
Dim stExpFile As String
' set variable to Match Exception file name
stMEFile = "PH_ME_ALL_EXCEPTION_DETAILS_CR*"
' set variable to Expedite file name
stExpFile = "PH_EXPEDITE_PO_LINES*"
Dim iCounter, intNoOfRecs As Long
Dim varreturn As Long


'execute the query and load the results into the variable for the recordset
rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
intNoOfRecs = rs.RecordCount

varreturn = SysCmd(acSysCmdInitMeter, "Loading new files into system ", intNoOfRecs)

' loop through the records until there are no more
Do While Not rs.EOF
'Run code
Debug.Print rs("filename")
Debug.Print rs("loaded")
iCounter = iCounter + 1
'Update the Progress Meter to (intCounter/intNoOfRecs)%
varreturn = SysCmd(acSysCmdUpdateMeter, iCounter)

If rs("filename") Like stMEFile And rs("loaded") = "0" Then
' Load the Match Exceptions file
Call LoadME(rs("filename"))
' set flag on Match exception file to loaded
rs("Loaded") = 1

End If

If rs("filename") Like stExpFile And rs("Loaded") = "0" Then
' Load the Expedite file
Call LoadEXP(rs("Filename"))
' set flag on Expedite file to loaded
rs("loaded") = 1
End If
'move to the next item in the recordset before looping the code
rs.MoveNext

Loop

'Remove the Progress Meter
varreturn = SysCmd(acSysCmdClearStatus)
' close the recordset
rs.Close
' set the rs variable equal to nothing to free memory
Set rs = Nothing
End Function
'**************************************END CODE**************************************

'**************************************START CODE**************************************
Private Sub LoadME(strfilename As String)

'set query names to variables

Dim strq1 As String
Dim strq2 As String
Dim strq3 As String
Dim strq4 As String
Dim strfilepath As String
Dim strtable As String
Dim strSQL As String

' location of files to be loaded
strfilepath = "T:\MasterDatabase\QRY_MASTER_PeopleSoft\POManagementReports\"
' query used to remove yesterdays records from working file
strq1 = "qry_Delete_Yesterdays_MatchExceptions"
' query used to move imported records to working file
strq2 = "qry_Add_Todays_MatchException_Data"
' query used to find new buyers and add to the buyers table list
strq3 = "qry_New_MatchException_Buyer"
' will be used to capture totals for history and reporting
'strq4 = TBD qry
' table used to import the records to before moving to working file
strtable = "tbl_Match_Exception_RawLoad"

' SQL code to clear out the records from yesterday if they still exist

strSQL = "Delete * from tbl_Match_Exception_RawLoad"

' clear out the loading table
CurrentDb.Execute strSQL

'load new file into raw table
DoCmd.TransferSpreadsheet acImport, 8, strtable, strfilepath & strfilename, False, ""

' remove yesterday's working file records
DoCmd.OpenQuery strq1
' move records from raw table to working table
DoCmd.OpenQuery strq2
' check for new buyers and insert new ones into buyers table
DoCmd.OpenQuery strq3
' capurture totals for history
'DoCmd.OpenQuery strq4

' SQL code to clear out the records from that were just loaded into the working table
strSQL = "Delete * from tbl_Match_Exception_RawLoad"

End Sub

'**************************************START CODE**************************************
Private Sub LoadEXP(strfilename As String)

'set query names to variables
Dim strq1 As String
Dim strq2 As String
Dim strq3 As String
Dim strq4 As String
Dim strfilepath As String
Dim strtable As String
Dim strSQL As String

' query used to remove yesterdays records from working file
strq1 = "qry_Delete_Yesterdays_Expedites"
' query used to move imported records to working file
strq2 = "qry_Add_Todays_Expedites"
' query used to find new buyers and add to the buyers table list
strq3 = "qry_New_Expedites_Buyer"
' will be used to capture totals for history and reporting
'strq4 = tbd
strfilepath = "T:\MasterDatabase\QRY_MASTER_PeopleSoft\POManagementReports\"
' table used to import the records to before moving to working file
strtable = "tbl_Expedites_RawLoad"
' SQL code to clear out the records from yesterday if they still exist
strSQL = "Delete * from tbl_Expedites_RawLoad"

' clear out the loading table ensure fresh data
CurrentDb.Execute strSQL

'load new file into raw table
DoCmd.TransferSpreadsheet acImport, 8, strtable, strfilepath & strfilename, False, ""

' remove yesterday's expedites from working table
DoCmd.OpenQuery strq1
' move today's expedites from raw table to working table
DoCmd.OpenQuery strq2
' check for new buyers and add them to the buyers table
DoCmd.OpenQuery strq3

' clear out the loading table clear memory
CurrentDb.Execute strSQL
'**************************************END CODE**************************************

End Sub

 

[bubba100]

The next time your data is greater than 253 rows try manually importing into a new table. After its imported open the table to design view and see how Access decided on the data type.
I have also seen that when the data in a column or row is removed Access will still think there is something there and try and import it. Like when there are row/column totals. In that case you have to delete that column or row.

 

[fb63]

Ok, going back and trying to reload old files, tried manually importing file into new table. Still cut off at column 32, instead of all 52. And brought in 255 records, including row 1 which has record count in B1 and column headers in row 2. So back to 253 records, instead of the 379 records in the excel files. So since data won't load normally, would it work to read the records in 1 at a time into a recordset

I have the following code to try and load it into a record set but I am getting an error "Run-time error 1004: Application-Defined or Object-defined error" on the Do Until line of the code.

Function GetDataFromWorkbook()
    
    Dim strtable As String, strfilepath As String, strfilename As String
    Dim wb As Workbook
    Dim db As Database, rec As Recordset
    Dim a, b, c, d, e, f As String
    strtable = "Table1"
    strfilepath = "C:\folder\"
    strfilename = "PH_DETAILS_CR-18634597.xls"
    Set db = CurrentDb
    Set rec = db.OpenRecordset(strtable)
'   Debug.Print rec
    Set wb = Workbooks.Open(strfilepath & strfilename, True, True)
    
    
   Debug.Print strfilepath & strfilename
    
    ' open the source workbook, read only
    
Do Until wb.Worksheets("Sheet1").Range(a).Formula = ""

    d = wb.Worksheets("Sheet1").Range(a).Formula
    e = wb.Worksheets("Sheet1").Range(b).Formula
    f = wb.Worksheets("Sheet1").Range(c).Formula
    rec.AddNew
    rec.Fields("Field1") = d
    rec.Fields("Field2") = e
    rec.Fields("Field3") = f
    rec.Update
    
    i = i + 1
    a = "A" & i
    b = "B" & i
    c = "C" & i
    Loop
    wb.Close False ' close the source workbook without saving any changes
    Set wb = Nothing ' free memory
    rec.Close
    db.Close
    
End Function

 

[fb63]

ok, now I am really stumpped. I am working on this from home where I have Access 2007, I tried just importing and got the same record restrictions. Now I have created a totatlly new db with only 1 table, with fields f1-f52. Then put in this code in a module and ran it. And it loaded just like it was supposed to. Now, is it possible somewhere in that other database is some field that will restrict the # records and if so, where could I find it. If not, should I just have the code in the original db open a new database and load the files into that db then have linked tables to pull in the data. Already utilized table from other db with 51k records, so there shouldn't be a limit on that.

Function loaddata()
Dim strtable As String
Dim strfilepath As String
Dim strfilename As String

strtable = "sheet1"
strfilepath = "C:\folder\"
strfilename = "PH_DETAILS_CR-18634597.xls"
Debug.Print strfilepath & strfilename

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strtable, strfilepath & strfilename, False, "sheet1!A3:AZ1200"

End Function