set excel formula from access vba

[trevor40]

Hi, i am trying to create a cell formula using access vba

the formula below works fine if i copy and paste it into the cell, but if i try to use access vba to put the value there it errors with application defined error.

i have tried various ways of using quotes around sat and sun but cannot get it to go from access vba to an excel formula.

this works when pasted into cell M8
=IF(OR(B13="SAT",B13="SUN"),0, IF(J13>8,8,J13))

this fails to put formula into the cell using access vba
myformula = "=IF(OR(B13="SAT",B13="SUN"),0, IF(J13>8,8,J13))"
Current_Worksheet.Range("M8").FormulaR1C1 = myformula

Any help would be great thanks.

[June7]

Try using apostrophe instead of quote to delimit SAT and SUN.

[trevor40]

that was the first thing i tried, single, double, tripple.

no go
"=IF(OR(B8='SAT', B8='SUN'),0, IF(K8>8,8,K8))"

this line
"=IF(OR(B8=" & "SAT" & ", B8= & "SUN" & "),0, IF(K8>8,8,K8))"
returns
=IF(OR('B8'=SAT, 'B8'= SUN),0, IF('K8'>8,8,'K8'))
with a name error

still trying all sorts of combinations.

[June7]

Excel certainly wants a quote mark.

Not seeing:

"=IF(OR(B8=""SAT"", B8=""SUN""),0, IF(K8>8,8,K8))"

Maybe use Chr(34) somehow.

Maybe change the spreadsheet to use day numbers instead of names.

[ssanfu]

What is the code to open the Excel spreadsheet?

Instead of a formula, can you put a number into a cell from Access? (this is to check if it is the formula or writing to Excel in general)

[trevor40]

Excel certainly wants a quote mark.

Not seeing:

"=IF(OR(B8=""SAT"", B8=""SUN""),0, IF(K8>8,8,K8))"

Maybe use Chr(34) somehow.

Maybe change the spreadsheet to use day numbers instead of names.

when i enter
"=IF(OR(B8=""SAT"", B8=""SUN""),0, IF(K8>8,8,K8))"
it puts the line below into the cell
=IF(OR('B8'="SAT", 'B8'="SUN"),0, IF('K8'>8,8,'K8'))

i will see if i can use day numbers to get around the syntax problem.

[trevor40]

using numbers i get the same problem
this line
Current_Worksheet.Range("M8").FormulaR1C1 = "=IF(OR(B8=6, B8=7),0, IF(K8>8,8,K8))"

puts this as the formula
=IF(OR('B8'=6, 'B8'=7),0, IF('K8'>8,8,'K8'))

i just can't see were the quotes need to be for this.

all my other actions are fine, export to excel, format and layout the excell sheet.

[ssanfu]

This is a very long shot, but try putting a space before and after the "=" sign and ">".

Current_Worksheet.Range("M8").FormulaR1C1 = "=IF(OR(B8 = 6, B8 = 7),0, IF(K8 > 8, 8, K8 ))"

[June7]

I just remembered I have VBA behind an Excel file that sets formulas into cells.

    For intCCount = 2 To 7
        Cells(intRCount, intCCount + 1).Select
        ActiveCell = "=IF(Results!$" & strCol & "$" & intRow & "=0, " & Chr(34) & Chr(34) & ", Results!$" & strCol & "$" & intRow & ")"
        intRow = intRow + 1
    Next intCCount
    intRCount = intRCount + 40
    intCCount = 3
    Cells(intRCount, intCCount).Select

[trevor40]

I just remembered I have VBA behind an Excel file that sets formulas into cells.

    For intCCount = 2 To 7
        Cells(intRCount, intCCount + 1).Select
        ActiveCell = "=IF(Results!$" & strCol & "$" & intRow & "=0, " & Chr(34) & Chr(34) & ", Results!$" & strCol & "$" & intRow & ")"
        intRow = intRow + 1
    Next intCCount
    intRCount = intRCount + 40
    intCCount = 3
    Cells(intRCount, intCCount).Select

thanks but i need to do it from access.
i will just open a saved workbook that has a list of the formulas and copy/ paste them into my cells.

thanks anyway.

[June7]

I know you want to do it from Access. I thought the code syntax would be workable from Access as well. I did a test and got this to work:

Dim oE As Excel.Application
Dim oW As Excel.Workbook
Dim oS As Excel.Worksheet
Set oE = CreateObject("Excel.Application")
Set oW = oE.Workbooks.Add
oE.Visible = True

ActiveCell = "=IF(OR(Sheet1!$B$8 = 6, Sheet1!$B$8 = 7), 0, IF(Sheet1!$K$8 > 8, 8, Sheet1!$K$8))"

oW.Worksheets("Sheet1").Range("M8").Formula = "=IF(OR(Sheet1!$B$8 = 6, Sheet1!$B$8 = 7), 0, IF(Sheet1!$K$8 > 8, 8, Sheet1!$K$8))"

This also worked:
ActiveCell = "=IF($B$8=0, " & Chr(34) & Chr(34) & ", $K$8)"
oW.Worksheets("Sheet1").Range("M8") = "=IF(OR(Sheet1!$B$8 = 6, Sheet1!$B$8 = 7), 0, IF(Sheet1!$K$8 > 8, 8, Sheet1!$K$8))"

I don't think your use of FormulaR1C1 is correct:
http://www.excelforum.com/excel-prog...mula-r1c1.html
http://answers.microsoft.com/en-us/o...1-6151fbcaf20f

[trevor40]

I know you want to do it from Access. I thought the code syntax would be workable from Access as well. I did a test and got this to work:

Dim oE As Excel.Application
Dim oW As Excel.Workbook
Dim oS As Excel.Worksheet
Set oE = CreateObject("Excel.Application")
Set oW = oE.Workbooks.Add
oE.Visible = True

ActiveCell = "=IF(OR(Sheet1!$B$8 = 6, Sheet1!$B$8 = 7), 0, IF(Sheet1!$K$8 > 8, 8, Sheet1!$K$8))"

oW.Worksheets("Sheet1").Range("M8").Formula = "=IF(OR(Sheet1!$B$8 = 6, Sheet1!$B$8 = 7), 0, IF(Sheet1!$K$8 > 8, 8, Sheet1!$K$8))"

This also worked:
ActiveCell = "=IF($B$8=0, " & Chr(34) & Chr(34) & ", $K$8)"
oW.Worksheets("Sheet1").Range("M8") = "=IF(OR(Sheet1!$B$8 = 6, Sheet1!$B$8 = 7), 0, IF(Sheet1!$K$8 > 8, 8, Sheet1!$K$8))"

I don't think your use of FormulaR1C1 is correct:
http://www.excelforum.com/excel-prog...mula-r1c1.html
http://answers.microsoft.com/en-us/o...1-6151fbcaf20f

thanks again for trying to get this done, the above may work but i have found that i have to use Sat and Sun in place of the numbers 6 and 7 (yes i know it should be 7 and 1) as the rest of the sheet has this information already in it. The text in the formula then creates an application defined error.

so anyway what i have decided is going to be the best is to use a formula sheet and copy the cells required into the excel report.
i have never needed to open an extra copy of excel to do this before so i would like to get some help on opening a second workbook, select and copy the required cells and then paste the formulas from them into the first workbook and then close the reference workbook.
the code i have so far is only sort of working. see sample below. hope you can help.


first bit of code works fine, opens the workbook and does all the formatting i need.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Employee Time Report Output", gg, True
Set Excel_Workbook = GetObject(gg)
Set Excel_Application = Excel_Workbook.Parent
Excel_Application.WindowState = xlMinimized
Excel_Application.Visible = True
Excel_Workbook.Windows(1).Visible = True
Excel_Workbook.Worksheets(1).Name = "Time Report Output"
Excel_Workbook.Worksheets("Time Report Output").Tab.ColorIndex = 37
Set Current_Worksheet = Excel_Workbook.Worksheets("Time Report Output")
Excel_Workbook.Worksheets("Time Report Output").Select
Current_Worksheet.Cells.Select
With Selection
Current_Worksheet.Cells.HorizontalAlignment = xlRight
Current_Worksheet.Cells.Font.Name = "Times New Roman"
End With
continues to format sheet...

this next bit is were i'm stuck.

the workbook does open but isn't switching to the correct workbook to paste the cells

Set Excel_Workbook1 = GetObject("c:\TimeTracK Formulas.xlsx")
Set Excel_Application1 = Excel_Workbook1.Parent
Excel_Application1.Visible = True
Excel_Workbook1.Windows(1).Visible = True
Current_Worksheet.Range("M8:P8").Copy
Excel_Workbook1.Windows(1).Visible = False
Set Current_Worksheet = Excel_Workbook.Worksheets("Time Report Output")
Excel_Workbook.Worksheets("Time Report Output").Select
Current_Worksheet.Range("M8:P8").PasteSpecial xlPasteFormulas

PS if i don't have the formulas in the same cells as they are going to be pasted into then the formulas all change to various cell references all over the place, one changed to cell YV122

also i can only put the $ sign in front of the column reference for the formulas as it is placed into a list and the rest of the fields update automatically with the correct row numbers.

[June7]

This works:

oW.Worksheets("Sheet1").Range("M8") = "=IF(OR(Sheet1!$B$8 = " & Chr(34) & "SAT" & Chr(34) & ", Sheet1!$B$8 = " & Chr(34) & "SUN" & Chr(34) & "), 0, IF(Sheet1!$K$8 > 8, 8, Sheet1!$K$8))"

[trevor40]

This works:

oW.Worksheets("Sheet1").Range("M8") = "=IF(OR(Sheet1!$B$8 = " & Chr(34) & "SAT" & Chr(34) & ", Sheet1!$B$8 = " & Chr(34) & "SUN" & Chr(34) & "), 0, IF(Sheet1!$K$8 > 8, 8, Sheet1!$K$8))"

Again thanks, of course things have changed and the office people have changed their formulas to incorporate the weekends and weekdays into one formula for each of - normal time, 1.5 and 2 times, they used to have separate code for each line on their worksheet and manually enter the data.

They did this so that when the formulas (from my reference sheet) are "put" into the cells of the list they automatically set all list rows.

so now i have to rethink the whole thing, I think that the simplest way is to use a preformatted excel sheet with all of thier formulas in it, and put my data from access into that. I have only ever used transferspreadsheet like the code below.

Can you help me with going in the other direction and using an existing spreadsheet and import the data from access into it. I expect that once the link is esablished and the data placed in, the formatting will be the same?

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Employee Time Report Output", gg, True
Set Excel_Workbook = GetObject(gg)
Set Excel_Application = Excel_Workbook.Parent
Excel_Application.WindowState = xlMinimized
Excel_Application.Visible = True
Excel_Workbook.Windows(1).Visible = True
Excel_Workbook.Worksheets(1).Name = "Time Report Output"
Excel_Workbook.Worksheets("Time Report Output").Tab.ColorIndex = 37
Set Current_Worksheet = Excel_Workbook.Worksheets("Time Report Output")
Excel_Workbook.Worksheets("Time Report Output").Select
Current_Worksheet.Cells.Select
With Selection
Current_Worksheet.Cells.HorizontalAlignment = xlRight
Current_Worksheet.Cells.Font.Name = "Times New Roman"
End With
Current_Worksheet.PageSetup.Orientation = xlLandscape
Current_Worksheet.Range("A1:P1").HorizontalAlignme nt = xlCenter
Current_Worksheet.Range("A1:P1").VerticalAlignment = xlCenter
Current_Worksheet.Range("A1:P1").Font.Bold = True
Current_Worksheet.Range("A1:P1").Interior.ColorInd ex = 14
Current_Worksheet.Range("A1:P1").Font.ColorIndex = 2
...
...

[ssanfu]

Have you seen Ken Snell's site:
http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm