Replies

Good Morning,

I appreciate all your efforts very much and wish I had your talent and knowledge.

I’ve tried everything under the sun to get either of your options to process when the path has a space in it and it doesn’t. It’s shame because it works beautifully when the path doesn’t include a space in the path.

The paths that I’m using for both options is a network path (see examples below).

\myCompanyMyPaththisonehas spaces — doesn’t work returns msg: “No files matching search pattern found!, exiting”

\myCompanyMyPaththisone_uses_underscores — works perfectly.

If you have any other suggestions, I’m open but I totally understand you have other projects to work on and again, appreciate all your efforts. My only other option for now, is to continue to run the batch file and manually load to Access.

Thanks so much for your help and guidance.

I am speechless….I love this. Thank you so much.

There are 2 minor problems–

1. If the path I’m searching has a space in it I get “no files matching search pattern”. If I put the Open/Close Quotes around the path (Dir1Data Sources) then I get “run time error 52” bad file name..

2. It doesn’t search sub folders

For #1, I don’t have a clue how to handle the space within the path.
For #2 I will see what I can do to modify the sub folder issue.

Again, thank you so much.

Retired,

I am currently just running the batch file which creates a text file. I manually open the newly created text file then copy and paste into my table. Yes very clunky but it’s the only way I know how to do it at this point. The one thing I want to add, that isn’t included in the batch process, is to capture the path and file size. I can’t thank you enough for all your help, it’s greatly appreciated.

Batch File:

Code:

@ECHO OFF


CLS

ECHO.
ECHO EXTRACT FILE NAMES JOB…

ECHO.
SET /P confirmRun=ARE YOU SURE TO CONTINUE(Y/N)?:

IF “%confirmRun%”==”” GOTO Error
IF “%confirmRun%”==”N” GOTO Error

ECHO.
SET /P sourcePath=KEY IN THE SOURCE PATH:

ECHO.
SET /P destPath=KEY IN THE DESTINATION PATH:

ECHO.
SET /P outPutFileName=KEY IN THE OUTPUT FILENAME(ex: FileName.txt):

ECHO.
ECHO CHECKING FOR DIRECTORIES…

ECHO.

IF exist “%sourcePath%” (ECHO %sourcePath% FOUND) ELSE ( 
ECHO %sourcePath% NOT FOUND
GOTO Error
)

ECHO.

IF exist “%destPath%” (ECHO %destPath% FOUND ALREADY) ELSE (
mkdir “%destPath%” 
ECHO %destPath% CREATED
)

SET “outputFileFullName=%destPath%%outPutFileName%”

ECHO.
ECHO COMPLETED CHECKING DIRECTORIES!!!


ECHO.
ECHO PREPARING RESULT…


for /b /s “%sourcePath%” %%i in (*.*) do @echo %%~nxi >>”%outputFileFullName%”


ECHO CHECK RESULT FILE HERE…

ECHO %outputFileFullName%

ECHO.
ECHO JOB COMPLETED SUCCESSFULLY…

ECHO.
ECHO.
SET /P confirmRun=JOB ENDS HIT ENTER TO EXIT:

EXIT

GOTO End
:Error
ECHO.
ECHO Job Ends! Bye bye!!
ECHO.
ECHO.
SET /P confirmRun=JOB ENDS HIT ENTER TO EXIT:
EXIT
:End

Retired Geek —

Yes I use the file names in my db and your suggestion sounds great, however, there are a couple of hurdles to overcome, the path name will change each month, i.e. \mypathProject2MM_MMMYY and I’m not very good with coding Array’s. I didn’t think anyone would want to assist with that so I didn’t ask. But would certainly love explore that option and give it a try.

Thanks.

Thank you sooooo much, it work beautifully!!!!

You are correct, the list of file names is in an Access table, tbl_FileNameList_v18 and field name is v18_FileName.

This report is required by the state and must include daily activities and must be in Excel or cvs format. For the most part, I would prefer a crosstab query w/in Access but over a 5 month period and capturing daily data, I think the best approach is export to Excel (the entire Project is in Access).

The spreadsheet would have the following:
Row Heading=User name (i.e. Row 1)
Column Heading = Date (i.e. Column A)
Cells w/in Column will be the counts (i.e. Row2 columnA)

I’m not sure how to get the data out of Access and automatically update the fields at correct intersection with the correct username and correct.

First, I apologize for the name error.

Secondly, I finally have the problem resolved. I accomplished this by the following:

I created a Public Function
Public strPVDID As String
Public Function GetPVDId() As String
GetPVDId = strPVDID
End Function

In the query for the report(s) I pass strPVDID as a parameter.

strPVDID changes as the code goes through the loop

AND–
Changed:str_ToFaxName = Me.txt_To
To:strPVDID = MyRS.Fields(“txt_HEDIS_Fax_To”).Value

Changed:str_ToFaxName & “_MRR.pdf”
To: str_MyFilename = strPVDID & “_HEDIS_MRR.pdf”

Thanks for all the help everyone.

Thanks R4. I’ve made the change as he suggested. The str_ToFaxName is the only variable I need from the recordset. The path is where the report resides. The report is named using the str_ToFaxName and it’s recordset has a parameter based on the form’s text field str_ToFaxName and is a pdf file. So, the path is static but the report name is not. Should I do something different there?

Since I made the change as suggested, the code does loop correctly now but now I get the same report. I’m so confused

Jeremy,

Let me be more clarify.
I have a bound form, recordset is qy_MYQRYName (generic for posting purposes). I would rather not use a form at all only adopted the form usage based on Microsoft Office’s code. I would prefer to simply use the qry as the recordset and code to loop through that. I simply don’t know how to make that happen and have tried several things. Again, I don’t get any errors and the code runs but only captures the first record. I would love some assistance with this if you could offer.

Thanks so much for your time.

Thanks RG, I will give this a try and update then.

I can’t thank you enough for the help. Thanks to your advice, I modified the code as you outlined and the entire process works as it should now.

Code:

On Error GoTo Err_cmdOpenQuery_Click
    Dim MyDB As DAO.Database
    Dim qdef As DAO.QueryDef
    Dim i As Integer
    Dim strSQL As String
    Dim strSQLGroup As String
    Dim strWhere As String
    Dim strIN As String
    Dim flgSelectAll As Boolean
    Dim varItem As Variant
    
    Set MyDB = CurrentDb()
    
    strSQL = “SELECT ApVenTest.VenNumID, ApVenTest.VenName, ApTest.GrossAmt” & vbCrLf & _
    “FROM ApTest INNER JOIN ApVenTest ON ApTest.VenNum=ApVenTest.VenNumID”
    
    strSQLGroup = “GROUP BY ApVenTest.VenNumID, ApVenTest.VenName, ApTest.GrossAmt” & vbCrLf & _
    “HAVING (((Count(ApTest.GrossAmt)) > 1))”
        
    ‘Build the IN string by looping through the listbox
    For i = 0 To lstVenNum.ListCount – 1
        If lstVenNum.Selected(i) Then
            If lstVenNum.Column(0, i) = “All” Then
                flgSelectAll = True
            End If
            strIN = strIN & “” & lstVenNum.Column(0, i) & “,”
        End If
     Next i
     
    ‘Create the WHERE string, and strip off the last comma of the IN string
    strWhere = ” WHERE [VenNumID] in (” & Left(strIN, Len(strIN) – 1) & “)”
    
    ‘If “All” was selected in the listbox, don’t add the WHERE condition
    If Not flgSelectAll Then
        strSQL = strSQL & strWhere & strSQLGroup
    End If
    
    MyDB.QueryDefs.Delete “qry_Vendor_rst”
    Set qdef = MyDB.CreateQueryDef(“qry_Vendor_rst”, strSQL)
    
    ‘Open the query, built using the IN clause to set the criteria
    ‘DoCmd.OpenQuery “qry_Vendor_rst”, acViewNormal
    Me.Requery
    
    ‘Clear listbox selection after running query
    For Each varItem In Me.lstVenNum.ItemsSelected
        Me.lstVenNum.Selected(varItem) = False
    Next varItem
    
    
Exit_cmdOpenQuery_Click:
    Exit Sub
    
Err_cmdOpenQuery_Click:
   If Err.Number = 5 Then
        MsgBox “You must make a selection(s) from the list”, , “Selection Required !”
        Resume Exit_cmdOpenQuery_Click
    Else
    ‘Write out the error and exit the sub
        MsgBox Err.Description
        Resume Exit_cmdOpenQuery_Click
    End If

I really appreciate your help. I added the debug.print and breakpoint. Below is the code from the immediate window.

Code:

SELECT ApVenTest.VenNumID, ApVenTest.VenName, ApTest.GrossAmt 
FROM ApTest INNER JOIN ApVenTest ON ApTest.VenNum=ApVenTest.VenNumID 
GROUP BY ApVenTest.VenNumID, ApVenTest.VenName, ApTest.GrossAmt 
HAVING (((Count(ApTest.GrossAmt))>1)) WHERE [VenNumID] in (10547)

I’m going to post my entire code so someone can perhaps show me the error of my ways.

Code:

On Error GoTo Err_cmdOpenQuery_Click
    Dim MyDB As DAO.Database
    Dim qdef As DAO.QueryDef
    Dim i As Integer
    Dim strSQL As String
    Dim strWhere As String
    Dim strIN As String
    Dim flgSelectAll As Boolean
    Dim varItem As Variant
    
    Set MyDB = CurrentDb()
    
       strSQL = “SELECT ApVenTest.VenNumID, ApVenTest.VenName, Abs([GrossAmt]) AS GrsAmt ” & _
             “FROM ApTest INNER JOIN ApVenTest ON ApTest.VenNum = ApVenTest.VenNumID ” & _
             “GROUP BY ApVenTest.VenNumID, ApVenTest.VenName, Abs([GrossAmt]) ” & _
             “HAVING (Count(ApTest.GrossAmt)>1)”

             
    ‘Build the IN string by looping through the listbox
    For i = 0 To lstVenNum.ListCount – 1
        If lstVenNum.Selected(i) Then
            If lstVenNum.Column(0, i) = “All” Then
                flgSelectAll = True
            End If
            strIN = strIN & “” & lstVenNum.Column(0, i) & “,”
        End If
     Next i
     
    ‘Create the WHERE string, and strip off the last comma of the IN string
    strWhere = ” WHERE [VenNumID] in (” & Left(strIN, Len(strIN) – 1) & “)”
    
    ‘If “All” was selected in the listbox, don’t add the WHERE condition
    If Not flgSelectAll Then
        strSQL = strSQL & strWhere
    End If
    
    MyDB.QueryDefs.Delete “qry_Vendor_rst”
    Set qdef = MyDB.CreateQueryDef(“qry_Vendor_rst”, strSQL)
    
    ‘Open the query, built using the IN clause to set the criteria
    DoCmd.OpenQuery “qry_Vendor_rst”, acViewNormal
    Me.Requery
    
    ‘Clear listbox selection after running query
    For Each varItem In Me.lstVenNum.ItemsSelected
        Me.lstVenNum.Selected(varItem) = False
    Next varItem
    
    
Exit_cmdOpenQuery_Click:
    Exit Sub
    
Err_cmdOpenQuery_Click:
   If Err.Number = 5 Then
        MsgBox “You must make a selection(s) from the list”, , “Selection Required !”
        Resume Exit_cmdOpenQuery_Click
    Else
    ‘Write out the error and exit the sub
        MsgBox Err.Description
        Resume Exit_cmdOpenQuery_Click
    End If
End Sub
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click

    DoCmd.Close
Exit_cmdClose_Click:
    Exit Sub
Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click
    

Thanks for the quick Response, however, I’ve removed the &; and still get the message. I’m open to any other ideas.