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
> Access Vba To Check If Data Exists In Wrokbook, Access 2016    
 
   
aggiemarine07
post Aug 8 2019, 04:01 PM
Post#1



Posts: 59
Joined: 7-January 11



So each week I have to through about 40ish different workbooks and check that there is information (doesnt matter what; just filled in) in columns A to V, X to AL, and AT. All of this data starts in row 20 but some of these workbooks only have one entry (i.e. just row 20) and others have 100 plus (i.e. row 120 or greater).

How would I go about writing a macro from MS Access that goes through all 40ish files to check that there is data in these specific cells and if it doesnt just pop up and MsgBox with the name of the file.

I looked through here for "VBA check if cells contain data" but came up to a few that were kind of close but they were more concerned with reformatting the data rather than just checking if it existed. Any help would greatly appreciated! Thanks!
Go to the top of the page
 
theDBguy
post Aug 8 2019, 04:42 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,381
Joined: 19-June 07
From: SunnySandyEggo


Hi. Like you said, there should be plenty of samples available to do most of this stuff but probably not one that has all of it. So, you could try to split the requirements into several slices and find the solution for each one until you have everything you can put together. For example, we could say your requirements are as follows:

1. Open the file browser for the user to select a folder containing all 40 workbooks to process (hint, search for FileDialog object)
2. List all files from a folder location
3. Use a loop to get each file name from the list created in step 2. Each file name retrieved will then be processes in the following steps
4. Use Excel Automation to open the file retrieve for each loop iteration
5. Use Excel Automation to grab information from the Excel file in a specific worksheet and cell
6. Use a comparison evaluation to determine if the value retrieved in step 5 means there's data or not
7. Based on the result of the comparison performed in step 6, display a message box

There's probably more steps after that, but I'm not clear yet what you want to do from there. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
aggiemarine07
post Aug 9 2019, 09:38 AM
Post#3



Posts: 59
Joined: 7-January 11



Thanks for the advice! What should I search for so that I'm at least pointed in the right direction? How using VBA do I check that a cell or an entire row contains data?
Go to the top of the page
 
ADezii
post Aug 9 2019, 10:46 AM
Post#4



Posts: 2,676
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. The Worksheet containing Data for the specific Ranges, is it the same for all Workbooks? If so, what is the name of the Worksheet?
  2. Is it sufficient enough to check for the existence of Data in Row 20 of your Ranges and if no Data exists, report same?

P.S. - A Msgbox to report the absence of Data in specific Cells would be cumbersome to say the least. I suggest using a Log File or Table to store the results.
This post has been edited by ADezii: Aug 9 2019, 10:52 AM
Go to the top of the page
 
aggiemarine07
post Aug 9 2019, 02:53 PM
Post#5



Posts: 59
Joined: 7-January 11



@ADezii To answer you questions:

-Yes, the Worksheet name is the same in every workbook ("PLOG")
-Yes, the columns are all the same but the rows vary from workbook to workbook (they all start on Row20)
-Yes, if I understand you second question correctly that should be sufficient so long as the code stops when all values in the row are blank (i.e. there is nothing after it)

Since everything is in Access, a table would work too.

How would I go about doing this?
Go to the top of the page
 
ADezii
post Aug 10 2019, 09:52 AM
Post#6



Posts: 2,676
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
As long as the code stops...

Need more specifics, sample data along with expected results would be nice.
Go to the top of the page
 
ADezii
post Aug 11 2019, 12:01 PM
Post#7



Posts: 2,676
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. I am still a little confused on a couple of points, but I had some free time and I created a Demo for you which hopefully will point you in the right direction. The following Code will Open all Workbooks (*.xls, *.xlsx) in the Folder defined by the Constant conPATH_TO_WBS. For the Worksheet PLOG in every Workbook, every Cell in Row 20 of the specified Column Ranges (A20:V20, X20:AL20, AT20) will be examined to see if it contains Data. If Data within these Cells is missing, the Workbook Name/Path, Worksheet Name, and Cell Address not containing Data is written to a Log Table named tblMissingData. After all is said and done the Table is now Opened in Read Only Mode for your Review.
  2. Code Definition:
    CODE
    Private Sub cmdTest_Click()
    On Error GoTo Err_cmdTest_Click
    'Must set a Reference to the Microsoft Excel XX.X Object Library (Early Binding)
    '***************************** USER DEFINED SECTION *****************************
    Const conPATH_TO_WBS = "C:\My Workbooks\"   'Folder containing all 40 Workbooks
    '****************************************************************************
    '****************** Declarations necessary for Automation Code *************************
    Dim appExcel As Excel.Application   'Excel Application Object
    Dim wkb As Excel.Workbook           'Workbook Object, set for each WB
    Dim sht As Excel.Worksheet          'Worksheet Object, set for Sheet
                                        'PLOG' in each Workbook
    Dim rngA_V As Excel.Range           'Will refer to Range A20:V20 in each Sheet
    Dim rngX_AL As Excel.Range          'Will refer to Range X20:AL20 in each Sheet
    Dim rngAT As Excel.Range            'Will refer to Range AT in each Sheet
    Dim rngRef As Excel.Range           'Will iterate Ranges above
    '****************************************************************************
    Dim strFile As String       'Represents each Workbook in conPATH_TO_WBS
    Dim strSQL As String        'SQL needed for Insert Missing Data into tblMissingData

    strFile = Dir$(conPATH_TO_WBS & "*.*", vbNormal)
    Set appExcel = New Excel.Application        'New Instance of Excel

    CurrentDb.Execute "DELETE * FROM tblMissingData", dbFailOnError     'Clear Results Table

    DoCmd.Hourglass True        'Indicate activity

    Do While strFile <> ""      ' As long as Dir$() returns a File Name, Loop
      Select Case Mid$(strFile, InStrRev(strFile, "."))     'Analyze File extensions
        Case ".xls", ".xlsx"    'Only process these Extensions
          Set wkb = appExcel.Workbooks.Open(conPATH_TO_WBS & strFile, True)     'Set Reference to Workbook
          Set sht = wkb.Worksheets("PLOG")      'Set Reference to PLOG Worksheet in ech Workbook
           Set rngA_V = sht.Range("A20:V20")       'Set Reference to Range A20:V20 in Worksheet
            For Each rngRef In rngA_V
              If rngRef.Value = "" Then     'Cell has no Data in it
                strSQL = "INSERT INTO tblMissingData ([Workbook],[Worksheet],[Address]) VALUES ('" & _
                          conPATH_TO_WBS & wkb.Name & "','" & sht.Name & "','" & rngRef.Address & "');"
                  CurrentDb.Execute strSQL, dbFailOnError
              End If
            Next
           Set rngX_AL = sht.Range("X20:AL20")     'Set Reference to Range X20:AL20 in Worksheet
            For Each rngRef In rngX_AL
              If rngRef.Value = "" Then     'Cell has no Data in it
                strSQL = "INSERT INTO tblMissingData ([Workbook],[Worksheet],[Address]) VALUES ('" & _
                          conPATH_TO_WBS & wkb.Name & "','" & sht.Name & "','" & rngRef.Address & "');"
                  CurrentDb.Execute strSQL, dbFailOnError
              End If
            Next
           Set rngAT = sht.Range("AT20")     'Set Reference to Range AT20 in Worksheet
            For Each rngRef In rngAT
              If rngRef.Value = "" Then     'Cell has no Data in it
                strSQL = "INSERT INTO tblMissingData ([Workbook],[Worksheet],[Address]) VALUES ('" & _
                          conPATH_TO_WBS & wkb.Name & "','" & sht.Name & "','" & rngRef.Address & "');"
                  CurrentDb.Execute strSQL, dbFailOnError
              End If
            Next
        Case Else
          'Ignore all other File Extensions
      End Select
        wkb.Close , False   'Close the Workbook, do not Save
        strFile = Dir$
    Loop

    appExcel.Quit
    Set appExcel = Nothing
    Set wkb = Nothing
    Set sht = Nothing

    With DoCmd
      .OpenTable "tblMissingData", acViewNormal, acReadOnly
      .Maximize
    End With

    Exit_cmdTest_Click:
      DoCmd.Hourglass False
        Exit Sub

    Err_cmdTest_Click:
      DoCmd.Hourglass False
        MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
          Resume Exit_cmdTest_Click
    End Sub
  3. OUTPUT after Code execution using two Workbooks:
    IDWorkbookWorksheetAddress
    67C:\My Workbooks\Book1.xlsxPLOG$F$20
    68C:\My Workbooks\Book1.xlsxPLOG$N$20
    69C:\My Workbooks\Book1.xlsxPLOG$Y$20
    70C:\My Workbooks\Book1.xlsxPLOG$AD$20
    71C:\My Workbooks\Book1.xlsxPLOG$AJ$20
    72C:\My Workbooks\Book1.xlsxPLOG$AT$20
    73C:\My Workbooks\Book17.xlsxPLOG$B$20
    74C:\My Workbooks\Book17.xlsxPLOG$J$20
    75C:\My Workbooks\Book17.xlsxPLOG$Q$20
    76C:\My Workbooks\Book17.xlsxPLOG$AA$20
    77C:\My Workbooks\Book17.xlsxPLOG$AG$20
    78C:\My Workbooks\Book17.xlsxPLOG$AT$20
  4. Realizing that this must all be very confusing, I over-commented the Coding and attached a Demo for you.

P.S. - Please keeping mind that I literally threw this together as a starting point for you. I am sure that it can be improved upon, but honestly, I do not have the time.
This post has been edited by ADezii: Aug 11 2019, 12:04 PM
Attached File(s)
Attached File  Open_Workbooks.zip ( 27.12K )Number of downloads: 1
 
Go to the top of the page
 
aggiemarine07
post Aug 13 2019, 11:45 AM
Post#8



Posts: 59
Joined: 7-January 11



@ADezii - thanks for all your hardwork on the code; ill check it out in the next day or so as I was out of down the last few days for personal reasons and am now catching up. More to follow....
Go to the top of the page
 
aggiemarine07
post Aug 14 2019, 01:14 PM
Post#9



Posts: 59
Joined: 7-January 11



@ADezii thanks for that code but i think I figured out a simpler solution that utilized queries after I imported the files into a table that does the job I need it to using the below code:

CODE
Function Import_Excel()
Dim myfile
Dim mypath

mypath = CurrentProject.Path & "\originals\"
myfile = Dir(mypath & "*.xlsx")

Do While myfile <> ""
  If myfile Like "*.xlsx" Then
        DoCmd.TransferSpreadsheet acImport, , "PLOGMissingData", mypath & myfile, False, "Whole Foods Market PLOG!A20:AT120"

Else

    Name mypath & myfile As mypath & myfile
End If
  myfile = Dir()
Loop


My final problem that I am trying to solve, is to import the filename that the data came from by inputting a SQL statement. What I have right now is this:

CODE
Function Import_Excel()
Dim myfile
Dim mypath
Dim sSql As String

mypath = CurrentProject.Path & "\originals\"
myfile = Dir(mypath & "*.xlsx")

Do While myfile <> ""
  If myfile Like "*.xlsx" Then
        DoCmd.TransferSpreadsheet acImport, , "PLOGMissingData", mypath & myfile, False, "Whole Foods Market PLOG!A20:AT120"
        sSql = "UPDATE PLOGMissingData" & _
               "SET PLOGMissingData.FileName = myfile" & _
               "WHERE PLOGMissingData.FileName Is Null"
        DoCmd.RunSQL sSql
Else


    Name mypath & myfile As mypath & myfile
End If
  myfile = Dir()
Loop


But using that code I get a "runtime error 3144: syntax error in update statement" message but I followed the code on the microsoft wiki and cant figure out what I'm messing up.
This post has been edited by aggiemarine07: Aug 14 2019, 01:15 PM
Go to the top of the page
 
ADezii
post Aug 14 2019, 01:45 PM
Post#10



Posts: 2,676
Joined: 4-February 07
From: USA, Florida, Delray Beach


Your Syntax is slightly off, try:
CODE
Function Import_Excel()
Dim myfile As String
Dim mypath As String
Dim sSql As String

mypath = CurrentProject.Path & "\originals\"

myfile = Dir(mypath & "*.xlsx")

Do While myfile <> ""
  If myfile Like "*.xlsx" Then
    DoCmd.TransferSpreadsheet acImport, , "PLOGMissingData", mypath & myfile, _
                              False, "Whole Foods Market PLOG!A20:AT120"
      sSql = "UPDATE PLOGMissingData " & _
             "SET PLOGMissingData.FileName = '" & myfile & "' " & _
             "WHERE PLOGMissingData.FileName Is Null"
        CurrentDb.Execute sSql, dbFailOnError
Else
  Name mypath & myfile As mypath & myfile
End If
  myfile = Dir()
Loop
End Function

P.S. - The WiKi Article references Literal Strings whereas you are referencing a Variable.
CODE
'Using a Literal String
"WHERE MyTable.[FileName] = 'Test.xlsx'"


CODE
'Using a Variable
Dim strFileName As String

strFileName = "Test.xlsx"
"WHERE MyTable.[FileName] = '" & strFileName & "'"

This post has been edited by ADezii: Aug 14 2019, 01:55 PM
Go to the top of the page
 
aggiemarine07
post Aug 16 2019, 08:32 AM
Post#11



Posts: 59
Joined: 7-January 11



worked; thanks! I knew i was missing something smile.gif
Go to the top of the page
 
ADezii
post Aug 16 2019, 08:42 AM
Post#12



Posts: 2,676
Joined: 4-February 07
From: USA, Florida, Delray Beach


yw.gif , good luck with your Project!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th October 2019 - 02:18 AM