Trying to create directories and auto-export reports to appropriate locations 2

[greyoxide]

I have a monthly report that is exported for each of the sales reps, detailing their advertisers activity. I have an access report which compiles all of these reports into a single multipage PDF. Which I must then use Acrobat to break into individual PDF's. These PDF's must then be placed in that reps folder on our shared drive.

Since the above is a time consuming process I am trying to a VBA module to do this for me in one step.

This is what I have so far

Public Function ExportToPDF()
  Const qryName = "MasterQuery"
  Const PK = "id"
  Const name = "Advertiser"
  Const Path = "path"
  Const dir = "repFname"
  Const rptName = "MasterReport"
  Dim rs As DAO.Recordset
  
  Set rs = CurrentDb.OpenRecordset(qryName)
  
  Do While Not rs.EOF
     
    'If dir(rs.Fields(Path) & "\" & rs.Fields(dir) & "\") Is "" Then
        'MkDir (rs.Fields(Path) & "\" & rs.Fields(dir) & "\")
    'End If
    
    DoCmd.OpenReport rptName, acViewPreview, , PK & " = " & rs.Fields(PK)
    DoCmd.OutputTo acOutputReport, rptName, acFormatPDF, rs.Fields(Path) & "\" & rs.Fields(dir) & "\" & rs.Fields(name) & ".pdf"
    DoCmd.Close acReport, rptName
    rs.MoveNext
  Loop
  End Function

I have set this up so that the path to the root folder is stored in one of my tables as "path". This would by queried in as the root folder, and the VBA would append "\" & Year[] & "\" & month[] & "\" repFname & "\", and place all of the individually exported PDF's within the appropriate folder.

Ultimately I want a folder for each month, then within that folder I want a folder for each rep, then that rep's files in their folder. I am storing the path in the database because this this report will export for a couple of divisions within the company, each of which use their own folder tree. This way you can enter the root directory once as an initial setup configuration.

My problem is the section of code which is commented out. The rest of the code seems to work well. I dont really know how to accomplish the creation of the folders.

 

[kjv1611]

try changing to:

    If dir(rs.Fields(Path) & "\" & rs.Fields(dir) & "\") = vbNullString Then
        MkDir (rs.Fields(Path) & "\" & rs.Fields(dir) & "\")
    End If

If that doesn't work, make sure the computer is working with what you THINK it's working with. So use Debug.Print to spit out that path to the Immediate Window. If you don't see it, press [Ctrl] + [G] on your keyboard

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

If still coming up odd, maybe try testing this way with Dir:

If Len(dir(rs.Fields(Path) & "\" & rs.Fields(dir) & "\")) = 0 Then

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[dhookom]

I usually use

If Len(Dir(.....)) = 0 Then [green]'need to add[/green]
   
End If

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

Hoew are ya greyoxide . . .

Path is a property of access and Dir is a function. So you can't use them as names. Also change Is to =

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

Good catch TheAceMan1. I would also recommend changing "Name" to something that isn't a property of everything.

Duane
Hook'D on Access
MS Access MVP

 

[greyoxide]

I have changed the logic block to:

    If dir(rs.Fields(pth) & "\" & rs.Fields(Fname)) = vbNullString Then
        MkDir (rs.Fields(pth) & "\" & rs.Fields(Fname))
    Debug.Print
    End If

(changed variable names to be unique from conventional function names.

This logic is still not working it makes it all the way through the loop one time, it creates a folder and puts the first PDF in the folder. However I cannot get it to skip over MkDir on the next run of the loop.

I get a "Run-time error '75':
path/file access error.

When I navigate to the root folder and delete the VBA created folder the script will work, again for the first run of the loop.

Also I am not certain where to put the Debug.print at so that I can get specifics.

 

[TheAceMan1]

How are ya greyoxide . . .

Try:

[COLOR=#204A87]If dir([highlight #FCE94F]""[/highlight] & rs.Fields(pth) & "\" & rs.Fields(Fname)[highlight #FCE94F]""[/highlight]) = vbNullString Then[/color]

[highlight #FCE94F][/highlight]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[greyoxide]

@Aceman1 Thanks for the help. I have to admit I'm not quite certain what your suggestion was. Is that like a wild card?

I tried it exactly as you ordered, And I am getting a

Compile error:
Expected list separator or)

It looks like it just dislikes the last quotes. Please advise if possible.

 

[greyoxide]

OK it looks like I was able to figure it out on my own. I used the code below.

    dst = (rs.Fields(pth) & "\" & rs.Fields(Fname))
     
    If Len(dir(dst, vbDirectory)) = 0 Then
        MkDir (dst)
    Debug.Print
    End If

Thanks to everyone for the help.