Hi,
I am designing a "BOT" to automatically look through a folder on a sharepoint site. It will open the files and grab information that I want from each file and enter into a database.
The first part is working which is the "Add" portion. I am having a hard time coming up with a way to make the update script work. It is only updating half of the records. I feel it has to do with the way I am looping through the script, but I cannot get my mind around it.
I basically have two loops: One looping through all the files in the folder and one looping through the lines on the excel sheets to pick up the "records".
I've attached the Access Code for now
The code is a mess....I know. I can provide an excel sheet to test by request. Basically, I run it with a fresh, empty table....it does perfect. I change all the [Metric_Value] to 0 and run it again, it only updates one set of values.
I am designing a "BOT" to automatically look through a folder on a sharepoint site. It will open the files and grab information that I want from each file and enter into a database.
The first part is working which is the "Add" portion. I am having a hard time coming up with a way to make the update script work. It is only updating half of the records. I feel it has to do with the way I am looping through the script, but I cannot get my mind around it.
I basically have two loops: One looping through all the files in the folder and one looping through the lines on the excel sheets to pick up the "records".
I've attached the Access Code for now
The code is a mess....I know. I can provide an excel sheet to test by request. Basically, I run it with a fresh, empty table....it does perfect. I change all the [Metric_Value] to 0 and run it again, it only updates one set of values.
Code:
Option Compare Database
Option Explicit 'Always do this
Sub CVall()
Dim Myfile As String
Dim xlApp As Object
Dim wb As Object
Dim ws As Object
'Define database
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim x As Integer
'Dims for dates
Dim zYear As Integer
Dim zMonth As Integer
Dim zFiscalYear As String
Dim zFiscalMonth As String
Dim zMetricKey As String
Set db = CurrentDb
Set xlApp = CreateObject("Excel.Application")
Const xlUp As Long = -4162
'Error control
xlApp.DisplayAlerts = False
'Go get file from location
Myfile = Dir("*ENTER WHERE YOU SAVED THE EXCEL FILE*")
'Cycle through all sheets and tabs
Do While Len(Myfile) > 0
Select Case True
'**************************-------------------------------------------------------------------------------------------------------PM Overview Report
Case Myfile Like "*FILENAME*"
Debug.Print "FILE FOUND"
Debug.Print Myfile
'Application.Run "ZeroCheck", Myfile
Set wb = xlApp.Workbooks.Open("LOCATION" & Myfile)
Set ws = wb.Worksheets("WORKSHEET NAME")
'rs2.MoveFirst
'Getting all the variables set before looping
zYear = Format((Date - 14), "yyyy")
zMonth = Month((ws.cells(4, 6).Value))
If zMonth >= 6 Then
zYear = zYear + 1
End If
zFiscalYear = zYear - 1 & "/" & zYear
'Fiscal Month Calculation
If Month((ws.cells(4, 6).Value)) >= 6 Then
zFiscalMonth = Month((ws.cells(4, 6).Value)) - 5
ElseIf Month((ws.cells(4, 6).Value)) <= 6 Then
zFiscalMonth = Month((ws.cells(4, 6).Value)) + 7
End If
'For x = 1 To 4
'Debug.Print "X ="; x
'Find last row based on column 4 starting at row 6
For i = 6 To ws.cells(ws.Rows.Count, 4).End(xlUp).Row
'Opening the Dashboard Metric table, looking for a match for: Plant Code, Area, Fiscal Year, Metric Month, and Metric Key
Set rs = db.OpenRecordset("SELECT * " & _
"FROM Z_Dashboard_Metrics " & _
"WHERE (((Z_Dashboard_Metrics.Plant_Code)='" & ws.cells(i, 4).Value & "') AND ((Z_Dashboard_Metrics.Plant_Area)='" & ws.cells(i, 3).Value & "') AND ((Z_Dashboard_Metrics.Metric_Month)=" & Month(ws.cells(4, 6).Value) & ") AND((Z_Dashboard_Metrics.Metric_Key)='" & ws.cells(i, 5).Value & ws.cells(5, 5).Value & "') OR ((Z_Dashboard_Metrics.Metric_Key)='" & ws.cells(i, 5).Value & ws.cells(5, 9).Value & "') AND ((Z_Dashboard_Metrics.Metric_Fis_Year)='" & zFiscalYear & "') );")
'"WHERE (((Z_Dashboard_Metrics.Plant_Code)='" & ws.cells(i, 4).Value & "') AND ((Z_Dashboard_Metrics.Plant_Area)='" & ws.cells(i, 3).Value & "') AND ((Z_Dashboard_Metrics.Metric_Month)=" & Month(ws.cells(4, 6).Value) & ") AND((Z_Dashboard_Metrics.Metric_Key)='" & ws.cells(i, 5).Value & ws.cells(5, 5).Value & "') OR ((Z_Dashboard_Metrics.Metric_Key)='" & ws.cells(i, 5).Value & ws.cells(5, 9).Value & "') AND ((Z_Dashboard_Metrics.Metric_Fis_Year)='" & zFiscalYear & "') );")
'"WHERE (((Z_Dashboard_Metrics.Plant_Code)='" & ws.cells(i, 4).Value & "') AND ((Z_Dashboard_Metrics.Plant_Area)='" & ws.cells(i, 3).Value & "') AND ((Z_Dashboard_Metrics.Metric_Fis_Year)='" & zFiscalYear & "') AND ((Z_Dashboard_Metrics.Metric_Month)='" & Month(ws.cells(4, 6).Value) & "') AND ((Z_Dashboard_Metrics.Metric_Key)='" & ws.cells(i, 5).Value & "'));")
'Debug.Print ws.cells(i, 4).Value
'Debug.Print ws.cells(i, 3).Value
'Debug.Print zFiscalYear
'Debug.Print Month(ws.cells(4, 6).Value)
'Debug.Print ws.cells(i, 5).Value
'If no Match, then add new.
If rs.EOF = True Then
Debug.Print "No Match Found, adding Record"
Debug.Print "Row "; i
'Completion Adherence------
rs.AddNew
rs![Plant_Code] = ws.cells(i, 4).Value
rs![Plant_Area] = ws.cells(i, 3).Value
rs![Metric_Fis_Year] = zFiscalYear
rs![Metric_Month] = Month((ws.cells(4, 6).Value))
rs![Metric_Fis_Month] = zFiscalMonth
rs![Metric_Alt_Key] = ws.cells(i, 5).Value
rs![Metric_Value] = FormatPercent((ws.cells(i, 6).Value), 0)
Debug.Print ws.cells(i - i + 5, 6)
Debug.Print i
Debug.Print i - 1
rs![Metric_Key] = ws.cells(i, 5).Value & ws.cells(5, 6).Value
rs.Update
'Total Due---------
rs.AddNew
rs![Plant_Code] = ws.cells(i, 4).Value
rs![Plant_Area] = ws.cells(i, 3).Value
rs![Metric_Fis_Year] = zFiscalYear
rs![Metric_Month] = Month((ws.cells(4, 6).Value))
rs![Metric_Fis_Month] = zFiscalMonth
rs![Metric_Alt_Key] = ws.cells(i, 5).Value
rs![Metric_Value] = ws.cells(i, 9).Value
Debug.Print ws.cells(i - i + 5, 9)
Debug.Print i
Debug.Print i - 1
rs![Metric_Key] = ws.cells(i, 5).Value & ws.cells(5, 9).Value
rs.Update
Else
Debug.Print "Row "; i
Debug.Print "Duplicate Found, Editing Record"
Debug.Print rs![Plant_Code]
Debug.Print rs![Metric_Alt_Key]
Debug.Print rs![Metric_Key]; " for "; ws.cells(i, 4).Value
'Debug.Print i
'Debug.Print ws.cells(i, 4).Value
'Debug.Print Month((ws.cells(4, 6).Value))
'Debug.Print ws.cells(i, 5).Value
'If you find a match, just update the Metric Value
rs.Edit
Select Case True
Case rs![Metric_Key] Like "*PM_1*"
Debug.Print ws.cells(i, 6).Value
rs![Metric_Value] = FormatPercent((ws.cells(i, 6).Value), 0)
Case rs![Metric_Key] Like "*PM_2*"
Debug.Print ws.cells(i, 9).Value
rs![Metric_Value] = (ws.cells(i, 9).Value)
End Select
rs.Update
End If
Next i
'Next x
'Close sheet/book
Set ws = Nothing
wb.Close False
Set wb = Nothing
'Move the file to the Archive folder
FileCopy "FILE FOLDER" & Myfile
Kill ("FILE FOLDER" & Myfile)
Myfile = Dir
Loop
Debug.Print "All Done!!"
End Sub
