Cycle through folder of Excel files and enter/update access database.

[cboggie]

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.

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

 

[strive4peace]

I didn't take the time to absorb your code but just wanted to pass on what I do when I loop through files to do something with them -- load the filenames into an array, and then cycle through the array ...

 

[cboggie]

I didn't take the time to absorb your code but just wanted to pass on what I do when I loop through files to do something with them -- load the filenames into an array, and then cycle through the array ...

even if the file names are random? The files get submitted with a common name with a series of random numbers after the name.

A little more info. The spreadsheet that the bot is reading contains 2 lines, starting at row 6. The two lines are identical except for metric key, metric alt key, and metric value. In the end, I want to capture two values per line.....column 6 and column 9.

 

[strive4peace]

yes because if I add, for instance, a DoEvents in the loop, I didn't get all the files

 

[cboggie]

Here is what the excel file that Im using to test looks like:

	1	2	3	4	5	6	7	8	9
2		Header Data
3		Header Data
4		Header data
5		Report	Area	Plant	Metric Alt Key	PM_1	XX	XX	PM_2
6			Hex	Geo	PR	100%			3
7			Hex	Geo	PDR	57%			47
8

<tbody>
</tbody>

I am looping through starting from row 6. Looking for a match of Area+Plant+Alt Key.....and either adding or updating columns 6 and 9.

My table in access looks like this after the initial load:

Plant_Code	Plant_Area	fis_year	Metric_month	Met_fis_month	Met_alt_key	Metric_key	Metric_value
Hex	Geo				PR		100%
Hex	Geo				PR		3
Hex	Geo				PDR		57%
Hex	Geo				PDR		47

<tbody>
</tbody>

If I delete all 4 Metric_Value's and reload it, it looks like this , only updating the PM_2 metric:

Plant_Code	Plant_Area	fis_year	Metric_month	Met_fis_month	Met_alt_key	Metric_key	Metric_value
Hex	Geo				PR
Hex	Geo				PR		3
Hex	Geo				PDR
Hex	Geo				PDR		47

<tbody>
</tbody>