+ Reply to Thread
Results 1 to 12 of 12

Automatically create file and fill in three cells from data in csv or other excel file

  1. #1
    Registered User
    Join Date
    04-12-2013
    Location
    14589
    MS-Off Ver
    Excel 2003
    Posts
    6

    Automatically create file and fill in three cells from data in csv or other excel file

    I'm not sure if this is possible, but here's what I'm hoping to do:

    I have a file (names.xlsx) that has 4 columns - FirstName, LastName, IDNumber, Building

    There are 126 rows of data in this file,

    I then have another excel file that is a template with all sorts of calculations. I need to fill in the name, ID, and building fields on the template.

    I really don't want to have to create 126 copies of the template and then type in the name, id, and building.

    I'm hoping that there is a way to have it automatically create a new file based on the template, fill in the 3 fields, and then save the file (ideally with the person's name as the file name and in a folder labeled with the building.

    IS THIS AT ALL POSSIBLE??

  2. #2
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Automatically create file and fill in three cells from data in csv or other excel file

    Yes that is possible using a macro. But you need to upload a small sample file to show how your data is laid out and how you wish it processed.

    Alf

  3. #3
    Registered User
    Join Date
    04-12-2013
    Location
    14589
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Automatically create file and fill in three cells from data in csv or other excel file

    contacts.xlsxtemplate.xlsx

    I'm attaching two files: contacts.xlsx with user data and template.xlsx that is the template

    There will be approx 125 rows of data in contacts.xlsx. The data will need to fill in cells D1, D3, D5 in template.xlxs

    Ideally, when the macro is run, it would create a copy of the template.xlsx for each row in contacts.xlxs

    It would then save the file name as 'lastname_firstname_ID.xls'

    Thank you in advance for any help you can provide!!

    - Jamie

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Automatically create file and fill in three cells from data in csv or other excel file

    Read the data from .csv file directly.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    04-12-2013
    Location
    14589
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Automatically create file and fill in three cells from data in csv or other excel file

    I'm trying to run the above Macro but I get the following error:

    Run-time error '429':
    ActiveX component can't create the object

    \1Click for large view - Uploaded with Skitch

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Automatically create file and fill in three cells from data in csv or other excel file

    Your excel version is not 2003.... but Mac...
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    04-12-2013
    Location
    14589
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Automatically create file and fill in three cells from data in csv or other excel file

    I apologize for that. I am using two workstations and didn't realize that the macro would have to be different. That means that I'll have to build it in 2003 as that's the machine it will need to run on.

    Also, I have 3 hidden worksheets. Does the code need to include the other 3 as well?

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Automatically create file and fill in three cells from data in csv or other excel file

    Have you tried the code?

  9. #9
    Registered User
    Join Date
    04-12-2013
    Location
    14589
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Automatically create file and fill in three cells from data in csv or other excel file

    Yes, it says runtime error '75'

    path/file access error

    I have the contacts.csv file in the same folder as template.xls

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Automatically create file and fill in three cells from data in csv or other excel file

    Hummmm, is it on the network?
    If so, do you have the right to access to the file?

    Anyway this code is assuming "Contracts.xlsx" is open.
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    04-12-2013
    Location
    14589
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Automatically create file and fill in three cells from data in csv or other excel file

    yes, this works great! it does not bring over the three other hidden worksheets though. i'm assuming that gets added in this line: With ThisWorkbook.Sheets("APPR Scoring Template")

    Not sure how to add it though...

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Automatically create file and fill in three cells from data in csv or other excel file

    Do you mean like this?
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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