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.
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).
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?
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:
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
Call xlEdit(xlsApp, cjob, xlFile)
Set xlsApp = Nothing
xlsApp.Visible = False
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
.sheets(jd1).Range("B" & jd1LR).Value = cjob
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.