Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access to Excel (2000)

    Hi,
    Could some kind soul explain to me the process of exporting data into an excel spreadsheet.....from scratch?
    Presumably I have a control on a form that exports the data (currently in 2 tables) into the excel spreadsheet. The excel document is currently stored in my documents.
    The Form in Access has a main record, with a One to Many relationship to the other form. So for instance, Joe Bloggs has a single entry(Main Form), with multiple address entries(SubForm).
    This will need to be done on a quarterly basis into a new excel spreadsheet. Will this need to be recreated each quarter, or can I use a template? Also how do I deal with the entries that have already been exported, can I somehow archive these?
    Any help would be vastly appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Access to Excel (2000)

    Your question is both broad and vague.
    1. Do you want to export the current record of the main form (together the corresponding records in the subform), or do you want to export all records of the main form (together with the corresponding records in the subform)?
    2. How should the subform records be exported?
    2a. To the same worksheet as the main form record (if so, what layout do you want?)
    2b. To another worksheet in the same workbook.
    2c. To another workbook.
    (More questions to come, no doubt)

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access to Excel (2000)

    Hi Hans,
    I have a main table, with a sub table. The Main table contains data that relates to Court files. The sub table relates to witnesses in relation to that file, so there can be multiple witnesses to each file.
    I've attached the excel workbook that I need to export the data to. The excel workbook cannot be changed as it is a nationwide template.
    Basically all the fields in the excel workbook are duplicated in access. Column A to I form the Main Table(TblMain), J to Z in the sub (TblWitness). The forms are titled FrmMain and FrmWitness.
    What I need to do is export this data to this excel sheet, quarterly.
    Generally I don't mind if the data in TblMain is duplicated for each witness onto each row in the excel workbook, or whether there is a main row (A-Z) in excel, followed by a row beneath (J-Z), however many additional witnesses.
    Never done this sort of thing before, so I don't know the best way to broach it!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Access to Excel (2000)

    Your attachment didn't make it to the post. If the spreadsheet is over 100 KB, attach it in a zip file.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access to Excel (2000)

    Attached...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Access to Excel (2000)

    You haven't answered my first question yet, so I'll repeat it:
    1. Do you want to export the current record of the main form (together the corresponding records in the subform), or do you want to export all records of the main form (together with the corresponding records in the subform)?

    New questions:
    2. Do columns A through I correspond exactly to the fields in the main table (in the same order)?
    3. Do columns J through Z correspond exactly to the fields in the sub table (in the same order)?

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access to Excel (2000)

    1. Yes, the data from both tables in Access will need to go into the single workbook in Excel.
    2. Yes
    3. Yes

    The only differences in both tables are a URNID field in both, Autonumber in TblMain and Number in TblWitness. And WitnessID(Autonumber) in TblWitness. (Image attached)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Access to Excel (2000)

    I'll ask it for the third time. Please read the question before replying.

    1. Do you want to export the current record of the main form (together the corresponding records in the subform), or do you want to export all records of the main form (together with the corresponding records in the subform)?

  9. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access to Excel (2000)

    Sorry, I thought I had covered this earlier. I will need to export all records from TblMain, with the corresponding records from TblWitness. This will need to be done on a quarterly basis. I'm guessing that the easiest way to do this would be for the user to select which quarter, and which year from comboboxes, but as to the exporting............... <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Access to Excel (2000)

    One option is to create an SQL string in code, open a recordset with this SQL string, loop through the records and use Automation to write the data into Excel.

    It's probably easier to approach this from the Excel side, i.e. import into Excel instead of exporting from Access. You'd still create an SQL string and open a recordset, then use the CopyFromRecordset method to import the records in one go.

  11. #11
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access to Excel (2000)

    Unfortunately, as it sounds the more complicated way, the former method will probably have to be used.
    The criteria for the project was that the data should be exported to excel from Access, essentially having a all singing/all dancing database.....
    Will this be overly complicated?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Access to Excel (2000)

    The coding is not trivial, but it can be done.

    You wrote that you want to select records by quarter. Which field is to be used for this? DateOutcome, or DateOffence, or something else?

    And could you attach a database (compacted and zipped) with just TblMain and TblWitness, and just a few dummy records in each? No need to include forms etc. Thanks.

  13. #13
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access to Excel (2000)

    Per quarter would be based on DateOutcome, going by the financial year, so from April to March.

    Attached is a zipped database with a few records in it.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Access to Excel (2000)

    I'll have a go at it, but not immediately.

  15. #15
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access to Excel (2000)

    Thanks Hans, very much appreciated.

Page 1 of 2 12 LastLast

Posting Permissions

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