Results 1 to 4 of 4
  1. #1
    sren is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    36

    Code to Export Query to a Network Folder Location

    Question:

    I have a number of queries set up like this one:

    Code:
    Private Sub ctlGenFormARE_Click()
    DoCmd.SetWarnings False
    'DoCmd.OutputTo acOutputQuery, "qry_Form_ARE", "ExcelWorkbook(*.xlsx)", "z:\2012_testing\Forms\ARE_FORM_MMDDYY.xlsx", False, "", , acExportQualityPrint
    DoCmd.OutputTo acOutputQuery, "qry_Form_ARE", "ExcelWorkbook(*.xlsx)", "z:\2012\Forms\ARE_FORM_MMDDYY.xlsx", False, "", , acExportQualityPrint
    MsgBox "Creation of Form for Bus Unit ""ARE"" has been completed.", vbInformation, "Export Complete"
    DoCmd.SetWarnings True
    End Sub
    Each query will output data for a given Business Unit to a specific network folder. The problem is that each time the path changes for a different project, I have to update the testing or production line of the code for each Business Unit. Very inefficient however when it was originally created, I thought it was more of a one-time thing.

    I have created a table that includes the output path in a field called "NetworkFolder_Path".
    Click image for larger version. 

Name:	Capture.PNG 
Views:	15 
Size:	11.7 KB 
ID:	12575

    I would like to have the code refer to the NetworkFolder_Path field in the query for the path to export the form to. I am not sure how to go about doing that. With my limited knowledge, I am not sure if I can set the path as a string, refer it back to the field, etc., like I have done for reports. My thought is that if I can go this route, any time the path changes, I will only have to update it in one place (i.e. in the main table, which each of the queries is linked to).

    Any suggestions will be greatly appreciated. Thank you for your time.



    Regards,
    sren

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe this will help you:
    Code:
    Private Sub ctlGenFormARE_Click()
       'Requires a reference set to Microsoft DAO 3.6 Object Library
       Dim r As DAO.Recordset
       Dim sSQL As String
       Dim FileName As String
       Dim FilePath As String
    
       'create file name
       FileName = "ARE_FORM_" & Format(Date, "mmddyy") & ".xlsx"
    
       sSQL = "SELECT NetworkFolder_Path FROM tbl_PathDirectoryInfo"
       sSQL = sSQL & " WHERE [ObjectType] = 'Form' AND [ObjectStatus] = 'Testing';"
       Set r = CurrentDb.OpenRecordset(sSQL)
    
       If Not r.BOF And Not r.EOF Then
          'get path
          FilePath = r!NetworkFolder_Path
    
          DoCmd.SetWarnings False
          'DoCmd.OutputTo acOutputQuery, "qry_Form_ARE", "ExcelWorkbook(*.xlsx)", "z:\2012_testing\Forms\ARE_FORM_MMDDYY.xlsx", False, "", , acExportQualityPrint
          DoCmd.OutputTo acOutputQuery, "qry_Form_ARE", "ExcelWorkbook(*.xlsx)", FilePath & "\" & FileName, False, "", , acExportQualityPrint
          MsgBox "Creation of Form for Bus Unit ""ARE"" has been completed.", vbInformation, "Export Complete"
          DoCmd.SetWarnings True
       Else
          'no records in recordset
          MsgBox "Error: Path not found" & vbNewLine & vbNewLine & "Action: Aborting!!!" & vbNewLine & vbNewLine & "What to do: Contact someone "
       End If
    
       'clean up
       r.Close
       Set r = Nothing
    
    End Sub

  3. #3
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742

    Description of A Solution

    Yeah, we techies spend a good chunk of our lives supporting apps that were designed as one-shots.

    As far as your export function, there are plenty of ways to do that. You already have a table that contains the desired output path, and you already have hard-coded DoCmd code that outputs the query to a file. You just need to get the path from the table into that DoCmd code.

    Based upon the run values you have stored (somewhere), you will query the table to get the path you want, and store that path (somewhere else).

    Here's a list of the "somewheres" that I've seen - (1) global variables (2) Temp variables (post-2010) (3) a hidden form that stays loaded but open (4) a control on the form that is actually running the process (5) a static (persistent) field inside the code for the form that is running the process.

    All of those will work. Just put the info in whatever place matches your current architecture, and then refer to it from there in the next step.

    After you have extracted the data from the table, you are going to use VBA to build the output file name using VBA string concatenation. Supposing the Path from the query is stored in a text box called txtPath on the current form, and you have a command button that will cause the export to run.

    Here's a snippet of building the filename and then executing the DoCmd. Depending on your operating system and organizational pathname standards, you might choose to concatenate single or double quotes around the concatenated name, if any file or folder name might include spaces. Here I used double-quotes - Chr(34).
    Code:
    Dim strFullPath As String
    Dim DQ As String
    DQ = Char(34)
    'build the full filename
    strFilePath = DQ & Me.txtPath.Text & "ARE_FORM_MMDDYY.xlsx" & DQ
      
    'output to that filename
    DoCmd.OutputTo acOutputQuery, "qry_Form_ARE", "ExcelWorkbook(*.xlsx)", txtFilePath, False, "", , acExportQualityPrint
    If you need help with the intermediate steps - how to get the Path out of the table and into a control or a temp variable or a global variable or whatever, just try it, and if you can't get it working, please post your code here and we'll kibbitz. On the other hand, if you DO get it working, please mark the thread solved.

  4. #4
    sren is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    36
    Yes....there are a number of things I would have done differently if I would have known this was going to be a repeat....

    Thanks to both of you. I will work through each of the ideas you both have presented, possibly trying both to determine what method may work best for the setup I have. If I have any additional questions, I will post those OR once I am comfortable with what I have, I will update to "solved".

    regards.
    sren

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Scan File to folder location from access
    By wrbuchanan2 in forum Access
    Replies: 3
    Last Post: 04-26-2013, 10:26 PM
  2. Replies: 10
    Last Post: 12-07-2012, 01:57 PM
  3. Replies: 17
    Last Post: 06-04-2012, 05:11 PM
  4. Replies: 23
    Last Post: 05-18-2012, 08:31 AM
  5. open folder/Make new folder(example)-VBA Code
    By Madmax in forum Code Repository
    Replies: 3
    Last Post: 03-13-2012, 09:17 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums