Assigning a value to an excel cell

[Les Isaacs]

Hi All

I have a form with a button whose event procedure currently copies a certain excel spreadsheet to a known path and file name, and then it opens the spreadsheet. The user then enters 5 values into 5 specific cells (always the same cells), then closes the spreadsheet, then clicks a second button that emails the spreadsheet to a recipient.

This all works fine, but it would be much better if the first procedure could assign the 5 values automatically to the 5 cells: the values are all available from the form that is currently open (and which has the two buttons). So in plain language I need something like:

copy the spreadsheet to the specified path and filename
open the spreadsheet
set spreadsheet cell D:13 to [forms]![frm mydata]![txtCode]
set spreadsheet cell T:22 to [forms]![frm mydata]![txtName]
set spreadsheet cell V:2 to [forms]![frm mydata]![txtCategory]
set spreadsheet cell W:42 to [forms]![frm mydata]![txtSerial]
set spreadsheet cell V:22 to [forms]![frm mydata]![txtStartDate]
Save and Close spreadsheet
Email spreadsheet

All the above is currently being done except the 5 'Set' lines! I'm sure this isn't too difficult, but despite googling it at length I can't seem to find the right method.

I should add that it is not an option to create the entire spreadsheet, as it is big and has various worksheets (the worksheet where the 5 values need to be added is always called "Inputs").

Hope someone can help.
Many thanks
Les

 

[Trevor G]

Les to get the workbook to open and then add the values you need something like this. Open your Database and also your Form in Design view. Then add a command button, from the command button you would select the Properties and Select the Event Tab on the On Click Event you would then select Code Module and then in the Code Screen you would select the Tools Menu and References, search down the list until you see Microsoft Excel XX.Object Library and click the box (XX being the version number you use).

Paste in the code below and adjust the few things I have shown. You haven't mentioned about what Email System you are using so you need to reveal this to get the code to create an email and display it.

Sub SomeCode()
Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
.Workbooks.Add 'Change to Open and add the full path and workbook name
.Worksheets("Sheet2").Select 'Change to Sheet Name
.Range("D13") = Me.txtcode
.Range("T22") = Me.txtname
.Range("V2") = Me.txtCategory
.Range("W42") = Me.txtSerial
.Range("V22") = Me.txtStartDate
End With
xlApp.ActiveWorkbook.Save = True
xlApp.ActiveWorkbook.Close
xlApp.Quit
End Sub

 

[Les Isaacs]

Hi Trevor

Many thanks for your reply - fantastic, as I think I'm almost there with your code, but there are two problems:

1. When I added the code and tried to compile, I got a "Compile error - expected Function or Variable" on the .Save part of xlApp.ActiveWorkbook.Save = True. I do have (and have always had) the reference to Microsoft Excel 14.0 Object library selected.

2. When I commented out everything after the End With (to get it to compile) and ran the code, stangely the workbook opened as a protected workbook - and when I tried to unprotect it I was asked for the password!!??

Out of curiosity I tried opening the workbook with the Call Shell command that I was previously using, and commenting out the .Workbooks.Open command, but although this succeeded in opening the workbook unprotected the code then failed with a runtime error 1004 - Method 'Worksheets' of object '_Application' failed, on the .Worksheets("Inputs").select line.

The code I now have is below.
Hope you can tell me where I go from here!!
Many thanks once agaoin
Les

Current code:
Dim strPathAndFileName
strPathAndFileName = "Z:\Client_Reports\" & [Forms]![staffs subform new]![Text544] & ".xls"

FileCopy "Z:\NHSP\Opt_out_calculator.xls", strPathAndFileName

' Call Shell("C:\Program Files\Microsoft Office\Office14\EXCEL.EXE " & """" & strPathAndFileName & """", 1)

Dim xlApp As Excel.Application

Set xlApp = CreateObject("Excel.Application")

With xlApp
.Visible = True
.Workbooks.Open strPathAndFileName
.Worksheets("Inputs").select
.range("D4") = Me.Text544
End With

xlApp.ActiveWorkbook.Save = True
xlApp.ActiveWorkbook.Close
xlApp.Quit

 

[Trevor G]

Les I have adjusted the code to see if it helps. As I can't use your database I haven't tested it, but let me know and if it doesn't then perhaps upload a stripped down version and I can then test it.

Sub testme()
Dim strPathAndFileName as String
strPathAndFileName = "Z:\Client_Reports\" & [Forms]![staffs subform new]![Text544] & ".xls"

FileCopy "Z:\NHSP\Opt_out_calculator.xls", strPathAndFileName

' Call Shell("C:\Program Files\Microsoft Office\Office14\EXCEL.EXE " & """" & strPathAndFileName & """", 1)

Dim xlApp As Excel.Application
Dim wksh As Excel.Worksheet 'Added to see if this helps
Set xlApp = CreateObject("Excel.Application")

With xlApp
.Visible = True
.Workbooks.Open strPathAndFileName
.wksh("Inputs").Select 'Used wksh
.Range("D4") = Me.Text544
End With

xlApp.ActiveWorkbook.Save 'removed = True
xlApp.ActiveWorkbook.Close
xlApp.Quit
End Sub

 

[Les Isaacs]

Hi Trevor

Many thanks for your continued help with this. Your code compiled fine, and ran to the line

.wksh("Inputs").select 'Used wksh

then errored with

runtime error 438 - object doesn't support this property or method.

Having defined wksh, should I be setting a value to it somehow?

The accdb that I have is extremely complex and it would take me quite a while to create a stripped down version - but I will if necessary! Perhaps though the error above is simple to resolve??

Thanks again
Les

 

[Trevor G]

Les I have adjusted a few things and made up a table called client and staffs in a database then created the workbook using your named workbook then placed a copy in a file location. Using the following code it works for me. In the code you would change the path and form details. When it gets to the Excel code rather than using the sheet name I am using its proper name, so if you open the workbook and then use Alt + F11 in the project window you will see the sheet names, you would use the real name not the stuff in speech marks.

Private Sub cmdtestme_Click()
Dim strPathAndFileName As String
strPathAndFileName = "M:\New\Shops\" & Forms.frmClient.frmStaffs.Form.staffs & ".xls"

Dim SourceFile, DestinationFile
SourceFile = "M:\New\Shops\Opt_out_calculator.xls" ' Define source file name.
DestinationFile = strPathAndFileName ' Define target file name.
FileCopy SourceFile, DestinationFile ' Copy source to target.

Dim xlApp As Excel.Application
Dim wksh As Excel.Worksheets 'Added to see if this helps
Set xlApp = CreateObject("Excel.Application")

With xlApp
.Visible = True
.Workbooks.Open strPathAndFileName
.Sheets(2).Activate 'Use sheet real name
.Range("D4") = Forms.frmClient.frmStaffs.Form.staffs 'Must reference to the proper control
End With

xlApp.ActiveWorkbook.Save 'removed = True
xlApp.ActiveWorkbook.Close
xlApp.Quit
End Sub

 

[Les Isaacs]

Hi Trevor

Still not quite there: I think the problem is my syntax with the line

.Sheet6.Activate 'Use sheet real name

The code errors on this line with runtime error 438 - object doesn't support this property or method.

I have attached the source file for you to see: the cells I need to assign data to are all on the sheet called Inputs.

I assume the reference to the sheet and cells will not be affected by the fact that the copied file will have a different path and file name to the source file?

I'm sure we're almost there!!
Thanks as ever
Les

 

[Trevor G]

You have an error on this line

.Sheet6.Activate 'Use sheet real name

It should be

.Sheets(6).Activate 'Use sheet real name

 

[Les Isaacs]

Hi Trevor
Now this is becoming annoying: with the sheet reference corrected (thanks for that!), I now get a message that the cell or chart is protected .... , and I can see that all but 4 cells are indeed protected, BUT cell D12 is one of the 4 that is not (I can edit it manually) but even when I amend the line to

.range("D12").value = [Forms]![staffs subform new]![Text544]

I get the "cell or chart is protected .... " message!!

The workbook (which I attached to my last post on this thread) has been provided by the NHS Pension Agency, who have obviously put in the protection etc. - but cell D12 is one that is intended to be edited and can be edited manually.

I have also noted that when I select two of the other unprotected cells, the cell address for D7 shows as "ActPay2013" and for D8 it shows as "pay_2013". Do I need to take this into account when referencing them for the value assignments from access?

Hope you don't give up on this!!
More thanks
Les

 

[Trevor G]

Perhaps changing the range reference to the named range will work

.range("D12").value = [Forms]![staffs subform new]![Text544]

to

.Range("ActPay2013").Value = [Forms]![staffs subform new]![Text544]

I have just tested using the named range and it works.

Sub name1()
Range("pay_2013").Value = 45

End Sub

 

[Les Isaacs]

Hi Trevor

OK - half way there!!
The first two assigments now work fine - with the cells referenced to the names (e.g. "ActPay2013" instead of D8)
but
The second two assignments are to cells that don't have 'names', and the code errors when trying to make these assignments:
.range("D10").value = calcNHSPRateThisYr([Text544])
and
.range("D12").value = [Tax code ltr] & [Tax code no]
with the message about the cell or sheet being protected: but I can edit these cells manually.

Is it always this messy trying to link access to excel?

As ever
Les

 

[Trevor G]

Les,

If they are protected as proper protection then you will need the password to unlock the sheet. If not then perhaps try assigning a name to the cell as part of the code then refer to the name as in the previous. Example of recorded macro to name a cell is shown below.

Sub Macro1()
'
' Macro1 Macro
'

'
Range("E5").Select
ActiveWorkbook.Names.Add Name:="YearTot", RefersToR1C1:="=Sheet1!R5C5"
End Sub

 

[Les Isaacs]

Hi Trevor

Apologies for not getting back to you yesterday - internet problems!

It's all fixed!!
The problem was that after assigning the first two values the active sheet somehow switched to the second sheet - all of which is protected. This was remedied by changing
.Sheets(6).Activate
to
.Sheets("Inputs").Activate
- which I now realise is what you told me to do when you gave me:
.Sheets(6).Activate 'Use sheet real name

So, everything is hunky dory and I'm extremely grateful for all your help.
Les

 

[Trevor G]

Les

Pleased to read you have a working solution.

Thanks for letting me know, and happy to help.

Trevor