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

 

[fb63]

Also forgot, MS Access 2003 and excel 2003.

 

[fb63]

Another issue I have discovered with the above code. It is not importing dates into the tables. I have tried it with the recieving table as text and as date to no avail. IF it is text, it holds the number value for the date, then transfering to record which has it as date, the value disappears.

 

[SkipVought]

If your date column has a mixture of dates (REAL DATES which are NUMBERS) and text or empty cells, you WILL have a problem getting complete data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fb63]

Well, I have all dates, expect the row 2 which has the field names. Row 1 has the record count in b1 Since it is variable record counts, is it possible to pull the value from b2 and use it in the range section of the transferspreadsheet? Right now, I dump it all into a rawdata table then append that to the working data table by query excludeding the first two rows by the values in the record count row and the field name row.

 

[SkipVought]

row 2 which has the field names. Row 1 has the record count

I believe THAT messes things up, as the query manager expects that FIELD NAMES are in row 1, and DATA is in rows 2 and following.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fb63]

So what is the best way to fix my issue? Since they wants hands free on the data, going in and manually deleting the first row is not an option.

 

[PHV]

Import a named range ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fb63]

Ok I have tried the named range as shown below,

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
MsgBox "loading ME file"
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, "Sheet1!A3:av1000"

and

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
MsgBox "loading EXP file"
'load new file into raw table
' added set row value 1000 till I can find way to find last row in excel
DoCmd.TransferSpreadsheet acImport, 8, strtable, strfilepath & strfilename, False, "Sheet1!A3:v1000"

Yet, both transfers only pull in columns a through v and 253 rows. That did fix the dates not coming in, but I am still missing where it grabs only to V on the ME file, instead of the AV column indicated. And why it stops at 253. Today's EX had 783 rows, the ME had 583. Still trying to find the faq that will let me detect the last row of the excel file to insert into the range. Yet, until I need to get the above range to work.

 

[SkipVought]

Is ALL your data contiguous?

faq68-1331

also change these to suite

Sub NameTable()
    With ThisWorkbook
        .Names.Add _
            Name:="[b]TableName[/b]", _
            RefersTo:="='" & _
                ActiveSheet.Name & "'!" & _
                ActiveSheet.[b][A1][/b].CurrentRegion.Address
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fb63]

Skip,

Yes, the data is contiguous, as the files dropped onto the network folder by Peoplesoft nightly. But I don't see how the table name has anything to do with the issue. Perhaps I am missing the correlation or I didn't provide enough data.

I am loading today's data from both files into the correct tables within the db. The EXP file load pulled in the following range A3:V255 and the ME file load pulled in range A3:V255 as well. Both are cutting the range short and the second file loaded cuts the off all columns past V, I even tried just loading the ME file to ensure the code wasn't keeping the values from EXP in but got the same results.

 

[fb63]

Could there be a recordset limit of 255 that I am hitting on this issue?

 

[SkipVought]

But I don't see how the table name has anything to do with the issue.

Are you not using Range Names to tranfer your data?

The Range Name defines the scope of the range, so no need to find last row etc.

There is no 255 row limit. Can transfer TENS OF THOUSANDS of rows.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fb63]

No,I am not using a range name, would naming the range, instead of listing it as "Sheet1!A3:V1000" make it work? When this is run, the only time it pulls in all records, is when there are less than 253, 1 file had 192 and it only pulled in those records, most days I have over 253 records and it stops at that number everytime no matter which file I load. The other file's range is "Sheet1!A3:AV1000" and it only pulls in A3:V255 leaving off the rest of the rows as well as not pulling in all the rows.

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
MsgBox "loading EXP file"
'load new file into raw table
DoCmd.TransferSpreadsheet acImport, , strtable, strfilepath & strfilename, False, "Sheet1!A3:V1000"


' 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**************************************
[\code]

 

[SkipVought]

would naming the range, instead of listing it as "Sheet1!A3:V1000" make it work?

You can answer that question by trying it.

There may be other issues related to the data. Hard to know without seeing it.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fb63]

Everything is working correctly today, all records were loaded today and all columns as well. I am not sure which issue it was but I changed all field in the recieving tables to text, no index and zero length allowed to resolve the issue. Not sure which one did the trick but after those changes, both files loaded fine.

 

[SkipVought]

you may have had a mixture of NUMERIC and TEXT data in the same column. That can mess things up!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fb63]

OK, now I am really stumped,

The files did not load today, going back to only 253 records, and 32 columns. I was able to get each file to load once correctly today, after deleting the table I load to and reseting the all the fields to text, 255, no index, not required, zero length allowed. But that only worked the first time I did that. Tried to reload the file and it went back to the original issue. Tried recreating the table again and it didn't work this time. Even tried deleting table and creating table in vba, didn't work. Table was created as follows

Dim dbs As Database
  Dim s As String
  s = "CREATE TABLE strtable ([F1] text, [f2] text,[f3] text, [f4] text, [f5] text, [f6] text, [f7] text, " & _
        "[f8] text, [f9] text, [f10] text, [f11] text,[f12] text, [f13] text, [f14] text, [f15] text, [f16] text," & _
        "[f17] text, [f18] text, [f19] text, [f20] text, [f21] text, [f22] text)"
Set dbs = CurrentDb
dbs.Execute s

and it created the table to tansfer the spreadsheet into but then the transfer command changed fields to dates, and numbers and only pulled in 253. Other than banging my head on my desk I am at a loss as to what to do to fix this issue.

 

[bubba100]

Skips correct (as usual), this really sounds like your DATA isn't consistant from day to day or within the same day.

If your date column has a mixture of dates (REAL DATES which are NUMBERS) and text or empty cells, you WILL have a problem getting complete data.

you may have had a mixture of NUMERIC and TEXT data in the same column. That can mess things up!

I had a field that was numeric at least that is what it looked liked, until I noticed in the Excel Formula Bar they had "centered" it using spaces.

 

[fb63]

Just got a new file in this afternoon that only had 196 records on it. All records pulled in, so I can't see how it would be a data issue, when it always stops at 253 if more but pulls everything under 253. If there was a data issue, then no matter how many records were on the file, it should exclude some.