Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2011
    Posts
    5

    Unanswered: Help output to saved spreadsheet

    Hi,

    I am trying to get data to be output to a spreadsheet from access. The idea is when a new job is created on the form, it opens a spreadsheet and copies the relevant information to the next blank row.

    To make things easier I have set it up so that the data to be output is put in a table with the same headings as the spreadsheet. The spreadsheets headings are held in row 2 as row 1 holds the database references for the HR system.

    Can anyone help?

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Link to the spreadsheet file. Once you do that, you can either make an APPEND query (to add many records) or use VBA to add one record at a time to the linked file, just like you would into an Access table.

    Just be careful of the structure. When linking a file to an Excel spreadsheet, the resultant table's structure, while loyal to the data, is not necessarily what you would want in an Access table - and there's not a thing you can do about it, except make sure the new data conforms to the linked file's structure.

    If you can't do that, you can still do the following:

    1 - import the spreadsheet (DoCmd.TransferSpreadsheet) into a new table.
    2 - add/append data as needed
    3 - Re-create the spreadsheet (DoCmd.OutputTo or DoCmd.TransferSpreadsheet).

    Don't be afraid to use the Help file.

    Good luck,

    Sam

  3. #3
    Join Date
    Jun 2011
    Posts
    5
    Thanks Sam,

    I will have a go, just to ellaborate further...

    There will actually be four sheets on the spreadsheet I need to add a line of data to the next blank row on each on the 4 sheets. Finally there is a 5th sheet that I need to insert the row number on to in 8 places.

    This spreadsheet is an import tool for the HR system and has links to the Business Rules Engine of the system, which means it validates the data as if it were going in through the frontend. The idea is that I populate the spreadsheet then call its run macro all from access.

    Do you have any code examples or links to show how to add a single line from a table to an excel sheet?

    Also I guess I want to check the primary key (in this case the new job code) doesn't already exist on the spreadsheet. Also I may need a table to log the error messages output from the spreadsheet.

    The errors are displayed at the end of the four rows on the sheet that I enter the row numbers (the batch sheet). I could read this back and store it in a table in access and get it to flag any "fails".

    Would you recommend late binding and infact do I need to bind at all? Final question can you add data to a spreadsheet without opening it, or open it silently from access?

    Cheers,

    Andy
    Last edited by mcinnes01; 06-29-11 at 05:50.

  4. #4
    Join Date
    Jun 2011
    Posts
    5
    Hi,

    Got a little further my question now is, I have the primary key and I can find the last cell and copy a value in to it. I also have all the row references to put on the batch sheet for the import. BUT I will have a lot of values from each of my 4 table to go on to the corresponding sheets. I'm guessing a SELECT statement using the primary key can be used but there will be a lot of aliases and then I would have to itterate accross the row to complete all the cells.

    The layout on my table is the same a the layout on the excel sheets, Can you copy a row from an access table and copy paste it to the excel sheet, or do I need to go through each cell as described above?

    This is my code upto now:

    Code:
    Sub OUT2XL(cjob As String)
    Dim xlsApp As Object
    Dim fpath As String, xlFile As String
    Dim job As String
    
    '   Full path of excel file to open
    xlFile = "NEW JOB GTR OUTPUT.xlsm"
    fpath = "\\########.LOCAL\personnel$\temp\TOOL BOX\JOB CODES\" & xlFile
    
    Set xlsApp = CreateObject("Excel.Application")
        On Error Resume Next
            xlsApp.UserControl = True
        On Error GoTo 0
         
        On Error GoTo ErrHandle
            xlsApp.Visible = True
            xlsApp.workbooks.Open (fpath)
    
        Call xlEdit(xlsApp, cjob, xlFile)
            
    ErrQuit:
        xlsApp.activeworkbook.Save
        xlsApp.Quit
        
        Set xlsApp = Nothing
    
    Exit Sub
    
    ErrHandle:
        xlsApp.Visible = False
        MsgBox Err.Description
        GoTo ErrQuit
    
    End Sub
    
    Sub xlEdit(xlsApp As Object, cjob As String, xlFile As String)
    Dim jd1 As String, jd2 As String, jd3 As String, REL As String, bch As String
    Dim jd1LR As String, jd2LR As String, jd3LR As String, relLR As String
        
    jd1 = "job details"
    jd2 = "job details ii"
    jd3 = "job details iii"
    REL = "relationships"
    bch = "batch"
    
        jd1LR = xlsApp.sheets(jd1).UsedRange.Rows.Count + 1
        jd2LR = xlsApp.sheets(jd2).UsedRange.Rows.Count + 1
        jd3LR = xlsApp.sheets(jd3).UsedRange.Rows.Count + 1
        relLR = xlsApp.sheets(REL).UsedRange.Rows.Count + 1
        
            With xlsApp
                .sheets(jd1).Range("B" & jd1LR).Value = cjob
            End With
        
    End Sub

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Hi, Andy,

    I admit not looking much at your code - I can only do this on lunch, which is limited - but my rule of thumb is this:

    If you are adding record(s) to a spreadsheet, where the result is a database lookalike, you can use Access in either of the ways I described above.

    If you are making changes on the spreadsheet, where the changes are dependent on the db's values, but the result not resembling a table, you can't. Period. What you need to do in that case is perhaps use any/all of the following tools: Microsoft Query, a lookup function, Excel macro written in VBA.

    What I suspect is that you will want to use Access to add the needed records into the spreadsheet, and then open the spreadsheet in Excel and use the tool(s) above to "distribute" the various data into individual cells.

    Incidentally, there is nothing wrong with opening Excel from within an Access VBA subroutine and working in real time.

    Good luck,

    Sam

Posting Permissions

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