Results 1 to 8 of 8
  1. #1
    AlexJ is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    19

    Question Need help exporting .xlsx with module developed by third party


    I have received from a third party thgough a client a database with a series of queries and macros to export civil survey data with attributes into excel once it is populated using an addon for Civil3D. The problem is the module that is ran to finish up and export the data has an absolute export path and I can't seem to figure out how to update it correctly. Without going too far into detail one table is populated with survey data, a new table is cleared and joined to this initial table, the data is then exported along with a series of similar tables. This is all accomplished by using a few simple macros to have all tables be loaded at once then exported. The final export is using the module in question. I am a newer Access and VBA user so editing an existing database and module is quite the challenge. Any help would be greatly appreciated.

    Here is the module:

    Option Compare Database


    Code:
    Function exportFile()
    On Error GoTo Macro1_Err
    Dim rs As Object
    Set rs = CurrentDb.OpenRecordset("select alignment.name from alignment")
    FileName = rs.Fields(0).Value
    rs.Close
        DoCmd.TransferSpreadsheet acExport, 9, "select - centerline pods load", "R:\Active\Filepathfromthirdparty" & FileName & ".xls", False, ""
        
     MsgBox ("R:\Active\Filepathfromthirdparty" & FileName & ".xls is done")
    Macro1_Exit:
        Exit Function
    Macro1_Err:
        MsgBox Error$
        Resume Macro1_Exit
    End Function
    Running it resulted in "... not a valid path. Make sure the path name is spelled correctly and that you are connected to the server on which the file resides," and changing the filepath to somewhere local (C:\Users\alexj\Documents\ for example) and running it resulted in "Failure creating file."

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The value "9" refers to an xlsx file, this may be the problem.

  3. #3
    AlexJ is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    19
    Hmm with that in mind, what would you recommend changing in that particular line to get a successful excel export?

    With this being the syntax of the line where I think the error is occurring:
    expression .TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)

    And these being the SpreadsheetTypes:
    Click image for larger version. 

Name:	Spreadsheet types.JPG 
Views:	14 
Size:	46.2 KB 
ID:	26064
    https://msdn.microsoft.com/en-us/lib.../ff196017.aspx


  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Either export it to xlsx using type 9, or xls using type 8

  5. #5
    AlexJ is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    19
    I tried what you said and received the same error. I replaced the whole "R:\Active\Filepathfromthirdparty" & FileName & ".xls", with just "FileName" in the hope that it would allow me to specify the save location manually or have it automatically save to MyDocuments which got me through that problem. Now I get a message indicating the TableName ("select - centerline pods load") isn't found anywhere in my database. I believe this is the report that gets created after the inital macros and queries are run. When I replace this with an object that is found in my database it exports fine leading me to believe the third party that designed this table was saving the "select - centerline pods load" report somewhere else or completely forgot to include it. I need to find a way to recreate this report or create a new one but I don't really know what it contained. Sounds like I have a new problem now. Thanks for the help.

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    "select - centerline pods load" is the name of either a table or a query (not a report), could be either.

    The statement requires the entire path name. Have you looked to see what is the value of "rs.Fields(0).Value"? I would assume that the recordset ("alignment") is a table, but may also be a query.

  7. #7
    AlexJ is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    19
    The reason I assume it is a report is because the database already had another similarly named report ("select - survey code report") in the reports section. The main goal is to export all the compiled tables comprised of joined survey/CAD/engineering data all at once which I assume would be easiest in report form with unique field names. Otherwise I would have to export multiple tables individually, which could probably be done by writing a line of code for each one but spitting it all out at once in different .xlsx files, but judging by the streamlined process of everything else I believe it was designed to be a one click operation. Any advice?

  8. #8
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Your original question was getting the export working. Now it seems that you are having trouble with the process that creates the data to be exported? I'm not sure what you are asking.

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

Similar Threads

  1. Replies: 4
    Last Post: 10-01-2016, 04:15 PM
  2. Third Party software in VBA
    By drunkenneo in forum Programming
    Replies: 1
    Last Post: 07-26-2013, 12:10 AM
  3. Replies: 2
    Last Post: 06-04-2012, 12:19 AM
  4. Replies: 4
    Last Post: 05-16-2011, 04:58 PM
  5. Replies: 1
    Last Post: 11-15-2010, 06:15 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