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

Welcome to UtterAccess! Please ( Login   or   Register )

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



Posts: 88
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
 
 
Start new topic
Replies
PaulBrand
post Oct 21 2019, 09:42 AM
Post#2



Posts: 1,751
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#3


UtterAccess VIP
Posts: 7,262
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#4



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#5



Posts: 88
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
 
DanielPineault
post Oct 22 2019, 03:56 PM
Post#6


UtterAccess VIP
Posts: 7,262
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
 

Posts in this topic



Custom Search


RSSSearch   Top   Lo-Fi    6th April 2020 - 11:23 PM