+ Reply to Thread
Results 1 to 4 of 4

Building a CSV file from existing Excel files used as a data entry forms

  1. #1
    Registered User
    Join Date
    05-21-2020
    Location
    Montreal, Canada
    MS-Off Ver
    365 Pro
    Posts
    4

    Building a CSV file from existing Excel files used as a data entry forms

    Context:
    I have a folder with thousands of excel files using the exact same template.
    the template looks like a printed form you might fill-in but has roughly 2000 cells containing information
    I want to build a CSV file from those in order to transfer the information into a new system

    Question:
    is there a way to use VBA in order to build what I need?

    Edit -- Have added example File
    Attached Files Attached Files
    Last edited by gaosiyou; 05-22-2020 at 02:09 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    360

    Re: Building a CSV file from existing Excel files used as a data entry forms

    Hi

    With VBA you can do anything.
    So you are asking to process all Excel files in a specified (or chosen) folder, convert the data within each file into say, a data record, append this record to a table etc etc etc.
    If you have 2000 info cells, well, that would take you up to column BXX so no problem there.

    maybe you could attach a sample file with 'cleansed/dummy' data, and tell us a little more of what data items you need etc etc etc

    zeddy

  3. #3
    Registered User
    Join Date
    05-21-2020
    Location
    Montreal, Canada
    MS-Off Ver
    365 Pro
    Posts
    4

    Re: Building a CSV file from existing Excel files used as a data entry forms

    That's pretty much right. I've already used VBA to "map" folder and then for each file in folder, retrieve a few fields. but wasn't sure about how to procede (always looking for better ways to do things).
    A suggestion I saw somewhere was to
    • open file
    • create array from fields in open file
    • paste array into destination table
    • close file
    • rinse and repeat for each file in folder

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Arrow Re: Building a CSV file from existing Excel files used as a data entry forms

    Hi !

    For each file in folder :

    • open File.
    • SaveAs VBA method with file format as CSV like when operating manually, easy with the Macro Recorder or reading VBA help.
    • Close file

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. data entry from excel to web forms
    By beepetark in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-08-2020, 02:35 AM
  2. design data entry forms to make a double entry
    By Paacweci1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-01-2019, 08:45 AM
  3. Replies: 3
    Last Post: 01-19-2014, 12:27 PM
  4. Building data entry page
    By Thupp in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-20-2013, 03:42 PM
  5. Macro to send data from one file to multiple other existing files
    By masben in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-09-2013, 05:21 AM
  6. Replies: 2
    Last Post: 08-28-2012, 03:12 PM
  7. Excel Add-In for Building Web Forms
    By atticforms in forum Excel General
    Replies: 0
    Last Post: 12-06-2011, 01:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1