UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Edit Excel Files Based On Query Result, Access 2016    
 
   
aggiemarine07
post Oct 21 2019, 08:32 AM
Post#1



Posts: 74
Joined: 7-January 11



So I am trying to make adjustments (primarily formatting) to multiple excel files within a folder based upon the results returned in a query (the query has the filename but not the filepath).

For example, I noticed that "ACTIVE" was missing in cell A2 in five excel files within the folder. I have a query that returned these five filenames and now I am trying to have a button to open each file up in order to add "ACTIVE" to cell A2 within only these five files.

I cobbled the below code together from two different websites (probably why it also looks REALLY ugly) on the internet but I cannot seem to get it work. I get no errors when I run it but also nothing happens to the files themselves. What am I missing?

CODE
Option Compare Database

Public Function CorrectionQueryFiles()
    On Error GoTo Error_Handler
    Dim db                    As DAO.Database
    Dim rs                    As DAO.Recordset
    Dim sSql                  As String
    Dim sFile                 As String
    
    Dim appExcel As Excel.Application
    Dim folderPath As String
    Dim Filename As String
    Dim wb As Workbook
    Dim Sh As Worksheet

    Set db = CurrentDb()
    sSql = "PLOGErrorsProductID"
    Set rs = db.OpenRecordset(sSql)
    
    folderPath = CurrentProject.Path & "\PLOGs\" 'contains folder path
    
    With rs
        If .RecordCount <> 0 Then
            Do While Not .EOF
                If IsNull(folderPath) = False And IsNull(![Filename]) = False Then
                    sFile = folderPath
                        If Right(sFile, 1) <> "\" Then sFile = sFile & "\"
                            sFile = sFile & ![Filename]
                                If Len(Dir(sFile)) > 0 Then
                                    '----------------
                                    wb.Open (folderPath & [Filename])
                                        'EDIT TO EXCEL DOC BEGIN------>
                                        Range("A2").Select
                                        ActiveCell.FormulaR1C1 = "ACTIVE"
                                        'END TO EXCEL DOC END----->
                                    wb.Save
                                    wb.Close False
                                    Set wb = Nothing
                                    Filename = Dir
                                    '-----------------
                        Else
                        'File does not exist
                        End If
                Else
                    'FilePath or FileName are null
                End If
                .MoveNext
            Loop
        End If
    End With

Error_Handler_Exit:
    On Error Resume Next
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    If Not db Is Nothing Then Set db = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: DeleteQueryFiles" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
    
MsgBox "Updated all PLOG files"
End Function
Go to the top of the page
 
arnelgp
post Oct 21 2019, 09:11 AM
Post#2



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


it would take less than a minute opening those five
workbooks and manually entering the value to A2 cell.

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
aggiemarine07
post Oct 21 2019, 09:41 AM
Post#3



Posts: 74
Joined: 7-January 11



@arnelgp - yep, in this specific situation you are correct, however I have to correct 208 for another item that is missing in that column of data in those excel spreadsheets. Just looking to make things efficient for this and other discrepancies I find; do you have a recommendation for how I could automate this?
Go to the top of the page
 
DanielPineault
post Oct 21 2019, 09:41 AM
Post#4


UtterAccess VIP
Posts: 6,974
Joined: 30-June 11



You need to do something along the lines of
CODE
Public Function CorrectionQueryFiles()
    On Error GoTo Error_Handler
    Dim db                    As DAO.Database
    Dim rs                    As DAO.Recordset
    Dim sSql                  As String
    Dim sFile                 As String
    Dim folderPath            As String
    Dim Filename              As String

    Dim oExcel                As Object 'Late Binding, so no need for the Excel reference library to be set.
    Dim oExcelWrkBk           As Object
    Dim oExcelWrSht           As Object

    On Error GoTo Error_Handler

    Set db = CurrentDb()
    sSql = "PLOGErrorsProductID"
    Set rs = db.OpenRecordset(sSql)

    folderPath = CurrentProject.Path & "\PLOGs\"    'contains folder path

    With rs
        If .RecordCount <> 0 Then

            On Error Resume Next
            Set oExcel = GetObject(, "Excel.Application")    'Bind to existing instance of Excel

            If Err.Number <> 0 Then    'Could not get instance of Excel, so create a new one
                Err.Clear
                On Error GoTo Error_Handler
                Set oExcel = CreateObject("Excel.Application")
                bExcelOpened = False
            Else    'Excel was already running
                bExcelOpened = True
            End If
            On Error GoTo Error_Handler
            oExcel.Visible = False   'Keep Excel hidden until we are done with our manipulation
            oExcel.ScreenUpdating = False

            Do While Not .EOF
                If IsNull(folderPath) = False And IsNull(![Filename]) = False Then
                    sFile = folderPath
                    If Right(sFile, 1) <> "\" Then sFile = sFile & "\"
                    sFile = sFile & ![Filename]
                    If Len(Dir(sFile)) > 0 Then
                        Set oExcelWrkBk = oExcel.Workbooks.Open(sFile)
                        Set oExcelWrSht = oExcelWrkBk.Sheets(1)
                        oExcelWrSht.Range("A2").FormulaR1C1 = "ACTIVE"
                        oExcelWrkBk.Close True
                    Else
                        'File does not exist
                    End If
                Else
                    'FilePath or FileName are null
                End If
                .MoveNext
            Loop
        End If
    End With

    If bExcelOpened = False Then    'Close excel if is wasn't originally running
        oExcel.Quit
    End If

Error_Handler_Exit:
    On Error Resume Next
    oExcel.Visible = True   'Make excel visible to the user
    oExcel.ScreenUpdating = True
    Set oExcelWrSht = Nothing
    Set oExcelWrkBk = Nothing
    Set oExcel = Nothing
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    If Not db Is Nothing Then Set db = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: CorrectionQueryFiles" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
    MsgBox "Updated all PLOG files"
End Function

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
PaulBrand
post Oct 21 2019, 09:42 AM
Post#5



Posts: 1,733
Joined: 4-September 02
From: Oxford UK


I think you probably need to .MoveLast to get a proper record count and .MoveFirst before your .EOF

--------------------
Paul
Go to the top of the page
 
DanielPineault
post Oct 21 2019, 09:44 AM
Post#6


UtterAccess VIP
Posts: 6,974
Joined: 30-June 11



QUOTE
I think you probably need to .MoveLast to get a proper record count and .MoveFirst before your .EOF

While this is true to get an accurate count (say you need it for a counter or something of the sort), but just to know if you have a record, you don't need to. So in this case, it's not a big deal. Either way will work.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
dmhzx
post Oct 21 2019, 11:40 AM
Post#7



Posts: 7,115
Joined: 22-December 10
From: England


Remove this line its closing end if

CODE
If .RecordCount <> 0 Then
---- Access may well report zero records if you don't MoveLast and then You'd need to Movefist again. And since you are doing something until eof.

And change from Do While not OF to Do Until .eof

do while will always try to run at lease one loop. (Possibly why you put the counter in.??)
Do until .eof won't run if the recordset is already at Eof, which it will be if there are no records, and won't be if there are any at all.


Use excel objects properly have a workbook object and a worksheet object, and set the sheet object to the sheet you want.
Then You don't need to use Select or Active Cell

You can just use

ws.range("A2") = "ACTIVE" (And you could do that an all sheets, not just the five you've found)

With your current code you are gambling that the sheet that was open when the file was last closed is the one you want to change. If you set a worksheet object to the actual sheet you want to change, you are in total control

Most of these suggestions are in Daniel's Code (Except the Do until, I hope I don't have while and until the wrong way round). I've just tried to explain the problem for you rather than give a solution.

Hope that helps

Go to the top of the page
 
aggiemarine07
post Oct 22 2019, 01:57 PM
Post#8



Posts: 74
Joined: 7-January 11



thanks everyone for yalls help with this!

@DanielPineault - I cannot seem to get your code working, it will flash an excel window and them immediately close. I placed msgbox's to try and figure out where the error is coming from and it seems that when it tried to get the excel object, it crashes and goes to the error handler. Any idea on how I can fix it?
This post has been edited by aggiemarine07: Oct 22 2019, 02:02 PM
Go to the top of the page
 
cheekybuddha
post Oct 22 2019, 02:15 PM
Post#9


UtterAccess VIP
Posts: 11,702
Joined: 6-December 03
From: Telegraph Hill


Hi aggiemarine07,

Are you sure that the code hasn't done what it is meant to?

The fact you end up in the error handler suggests that the error occurred after the instance of Excel has been obtained.

If you look at the code, once the object variable oExcel has been set, the instance of Excel is hidden from view (your flash and then disappearing).

I suspect the error is caused because the following lines of code should come later than they do:
CODE
' ...
    If bExcelOpened = False Then    'Close excel if is wasn't originally running
        oExcel.Quit
    End If
' ...


Cut and paste them in the Error_Handler_Exit section, before the line setting oExcel = Nothing:
CODE
' ...
Error_Handler_Exit:
    On Error Resume Next
    oExcel.Visible = True   'Make excel visible to the user
    oExcel.ScreenUpdating = True
    Set oExcelWrSht = Nothing
    Set oExcelWrkBk = Nothing
    If bExcelOpened = False Then    'Close excel if is wasn't originally running
        oExcel.Quit
    End If
    Set oExcel = Nothing
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
' ...


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Oct 22 2019, 03:02 PM
Post#10


UtterAccess VIP
Posts: 11,702
Joined: 6-December 03
From: Telegraph Hill


Actually, I missed that Daniel had an On Error Resume Next line in the exit section, so my guess will be wrong.

If there is an error, what message pops up?

--------------------


Regards,

David Marten
Go to the top of the page
 
DanielPineault
post Oct 22 2019, 03:56 PM
Post#11


UtterAccess VIP
Posts: 6,974
Joined: 30-June 11



When I tested it, it did the same. The entire thing only took a fraction of a second to accomplish. Did you check the Excel file to see if the files were updated?

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th November 2019 - 06:40 PM