Excel manipulation from Access

[Bruce927]

I have created code that will manipulate an excel workbook exported from access. I have been successful in formatting cells and column/row sizes. But what I would like to do is insert a formula into specified cells. my codes creates a work book with 4 using different query's as a 4 tabs in the workbook.
One of the query's that I export in to execl, I have inserted 3 extra columns which I have named and used "" to indicate they are blank.
Then a second module kicks in to mod the formats of the spreadsheet. then I want to insert a vlookup formula in each of the columns that looks at the other 3 spread sheets in the work book and returns the required information. this is the formula I would like to insert and fill down the column of the first spread sheet. IN CELL B2 =if(ISNA(VLOOKUP(A2,TAB2!A:A,1,0)=TRUE),"",Y) THEN IN C2 the same formula looking at tab3, then in d2 look at tab4. finally select the range of cells b2:D1252 and fill the formula down.
6/28/2017...
I have had some success in creating a new column in the first query to be exported to the workbook, this is what I typed 3000: "=IF(ISNA(VLOOKUP(RC[-1],_3000!C[-1],1,0)= true),"""",""Y"")". This allowed the formula to populate all cells in the column named _03000, the formula looks like this in excel =IF(ISNA(VLOOKUP(RC[-1],_3000!C[-1],1,0)),"","Y")but it will not calculate. it shows up as text. I have to go to the options and in the formula section select R1C1 referencing and then put the cursor in the cell before the = and hit back space then enter, then the formula will work in that cell. I guess my new question is how do I get it to work in the process of manipulating excel from Access?

[ADezii]

My guess would be, since you are using Automation Code, you would have to preface the ISNA () and VLOOKUP () Functions with the Instantiated Excel Application Object & Worksheet function as in:

'appExcel Object previously Declared and Instantiated
appExcel.WorksheetFunction.VLookup (arguments)
 

[ADezii]

I do believe that I was incorrect in my first assumption. I created what I feel is a work-a-around for propagating a similar type Formula from Columns B thru F referencing Tabs 2 thru 6.

Dim appExcel As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim lngCtr As Long
 
Set appExcel = New Excel.Application
    appExcel.Visible = True
 
Set wkb = appExcel.Workbooks.Open("C:\Test\Test.xlsx")
Set wks = wkb.Worksheets("Sheet1")
 
With wks
  .Activate
    For lngCtr = 2 To 6   'Cols B to F, Tabs2 thru Tab6
     .Cells(2, lngCtr).Formula = "=If(ISNA(VLOOKUP(B12,Tab" & CStr(lngCtr) & _
                                 "!A1:C300,3,True)),True,False)"
    Next
End With
 
wkb.Save
 
wkb.Close False
Set wkb = Nothing
appExcel.Quit
Set appExcel = Nothing

[NeoPa]

Hi Bruce.

It may help to show what you have in the way of code already. Excel Ranges have a .FormulaR1C1 property that you can use regardless of the interface setting of R1C1 / A1 notation for addresses. I can see you know how to use R1C1 notation, but not whether you've coded it correctly.

As a general rule I use the .FormulaR1C1 property to set formulae from code. It's a lot easier and more powerful than using A1 notation.

[Bruce927]

ADezii, and NeoPa thank you for your responses, these have been helpful. This is what I ended up with and it works very well.

Dim objExcel As Object
Dim objDoc As Object
Dim oExcel As Excel.Application
Dim c As Object
Dim i As Long
Dim DelRange As Range
Dim strExFormula01, strExFormula02, strExFormula03 As String
 
strExFormula01 = "=IF(ISNA(VLOOKUP(RC[-1],_3000!C[-1],1,0)),"""",""Y"")"
strExFormula02 = "=IF(ISNA(VLOOKUP(RC[-2],_4000!C[-2],1,0)),"""",""Y"")"
strExFormula03 = "=IF(ISNA(VLOOKUP(RC[-3],_5000!C[-3],1,0)),"""",""Y"")"
 
Set oExcel = New Excel.Application
Set objExcel = CreateObject("Excel.Application")
Set objDoc = objExcel.Workbooks.Open(sFile).Sheets(1)

then in the next section where I am formatting specified ranges in the spread sheet I used...

Application.columns("A:A").Select
.Application.Selection.ColumnWidth = 18
.Application.Range("B:D").Select
.Application.Selection.ColumnWidth = 1.71
.Application.Range("B2:B1100").Formula = strExFormula01
.Application.Range("C2:C1100").Formula = strExFormula02
.Application.Range("D2:D1100").Formula = strExFormula03
.Application.columns("E:E").Select
.Application.Selection.ColumnWidth = 44.78
.Application.Range("F:J").Select
.Application.Selection.ColumnWidth = 8.14
.Application.Range("K:BN").Select
.Application.Selection.ColumnWidth = 10.29
.Application.Selection.numberformat = "mm/dd/yy"
.Application.Range("A:BN").Select

This works very well, by declaring the string text as a formula it added the formula to the cells in the specified range. the only thing I will need to keep in mind is that some day the information that I export to excel may exceed the 1100 rows specified in the codes ranges. Then I will change that number.

[NeoPa]

Hi Bruce.

Some of that code looks highly dodgy to me. The case of the names looks like you haven't copied and pasted this from a compiled project.

Also, I've never tried using R1C1 format in .Formula. That's what .FormulaR1C1 is for that I told you about in post #4. Maybe it recognises the format and interprets it correctly. Personally I don't like for the compiler to have to interpret illogical code so I'd definitely use .FormulaR1C1.

At the end of the day though, if you're happy then we must be too. It's always harder to focus on the finer points when you're just getting to grips with the basics. I understand that.

[ADezii]

1. I am confused regarding the . qualifier for the Application Object in Lines 2 to 15, e.g. .Application. Application is the top level Object and needs no qualifier.
2. You could also clean up and visually enhance the Code a little:
   

   With Application
     .Columns("A:A").Select
      .Selection.ColumnWidth = 18
       .Range("B:D").Select
      .Selection.ColumnWidth = 1.71
       .Range("B2:B1100").Formula = strExFormula01
       .Range("C2:C1100").Formula = strExFormula02
       .Range("D2:D1100").Formula = strExFormula03
     .Columns("E:E").Select
      .Selection.ColumnWidth = 44.78
       .Range("F:J").Select
      .Selection.ColumnWidth = 8.14
       .Range("K:BN").Select
      .Selection.ColumnWidth = 10.29
      .Selection.NumberFormat = "mm/dd/yy"
       .Range("A:BN").Select
   End With

[NeoPa]

Sometimes, and especially when dealing with Application Automation, the code will have to refer to the .Application object of a separate application. While it's possible this will be a property of a separate object, it's generally assigned to a variable instead.

@ADezii.
Apologies for disagreeing with your indentation advice but that is a very rare occasion where your advice was not of the wisest. I suspect this is from a lack of classical education in IT. I believe you've picked most of your vast knowledge up bit by bit over the years.

Indentation is never random. It should always be to illustrate groupings. Thus, all the code in a procedure would be indented. Within that, any multi-command groupings should also be indented together. Examples of multi-command groupings are If ... Else ... End If, Do ... Loop, While ... Wend, Case (Within Select Case ... End Select), With ... End With, etc.

Each of the blocks of code that go to make up the group of commands bounded by the start and end commands must be indented together. This gives important visual clues as to the structure of the code and makes following the code a great deal easier. When indenting is present, but doesn't strictly follow these rules, then chaos reigns.

Thus, badly indented code is much worse than non-indented code. Non-indented code is much worse than properly indented code. This is a very important issue for maintainability of code.

[ADezii]

@NeoPa:
Points well taken and appreciated.