Results 1 to 6 of 6
  1. #1
    Tom123456 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2017
    Posts
    7

    VBA to export each table row as it's own excel file

    Hello,



    I have a piece of VBA that will split each row in a table into it's own excel file.

    However it does not work unless there is an empty field in the output (and source/dest tables) - is there any way to get this to work without exporting files that contains a empty columns at the start?

    Thanks,
    Tom
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,695
    you are working with an sql database. There is no need to copy 1 record at a time, 1 field at a time.
    use queries.
    number the records

    then make a query to pull 1 record:
    qsOutput = select * from input where recNo = forms!myForm!txtCount

    it stores the X on the form and the query reads it to export only that 1 record.
    then run thru the list exporting the query.
    Code:
    iCount = Dcount("*","input")
    For x = 1 To iCount
         forms!myForm!txtCount = X
         sFile = "c:\temp\MyFile" & format(x,"00") & ".xls"
    
    
         docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel12, "qsOutput", sFile, true
    
    
    next

  3. #3
    ssanfu is online now Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,199
    There are several errors that should be corrected.

    1) You have SPACES in field names and you have special characters in field names.
    Object naming rules:
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not use look up FIELDS, multi-value fields or calculated fields in tables.
    Do not begin object names with a number.

    2) "Name", "Input" and "Output" are reserved words and shouldn't be used for object names.
    Change "Input" and "Output" to "tblInput" and "tblOutput". (make changes in the code also)

    3) The path variable looks suspect. Why are there two dots in the path"??
    4) The last character in the path variable MUST be a back slash.
    Code:
    path = "\\nas01\kdrive\..Subscriptions\Data\Workbooks (CRM)\Delegates\output\output\"
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  4. #4
    Tom123456 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2017
    Posts
    7
    Hi Ssanfu/Ranman

    Thanks for looking at my query - I was able to update this so it uses a SQL select query that does not include the empty field.

    I'll bear the object naming rules in mind going forward - just using access here as a small part of a process to take a file and split out the rows and nothing else so I'm hoping it wont cause any issues - I needed the output file headers to match the current field names in the tables but i guess this should be renamed in a select statement in best use?

    Also thanks for point 4 - didn't realize it needed a backslash!

    Tom

  5. #5
    ssanfu is online now Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,199
    Glad you were able to get it to work...
    Ready to mark this solved?

    Gook luck with your project....
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  6. #6
    Tom123456 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2017
    Posts
    7
    Done - thanks for your help as well as the general pointers very useful.

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

Similar Threads

  1. Replies: 5
    Last Post: 05-25-2016, 12:43 PM
  2. Export to Excel as a shared file
    By ran-d in forum Programming
    Replies: 7
    Last Post: 07-23-2013, 04:04 PM
  3. Replies: 2
    Last Post: 08-05-2012, 06:32 PM
  4. Export all tables to 1 excel file
    By vestlink in forum Programming
    Replies: 5
    Last Post: 10-03-2011, 02:45 AM
  5. Export to excel (File Name issues)
    By fpmsi in forum Import/Export Data
    Replies: 6
    Last Post: 09-22-2011, 02:09 PM

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 - Senior Forums