By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,234 Members | 1,832 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,234 IT Pros & Developers. It's quick & easy.

Excel manipulation from Access

P: 23
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?
Jun 27 '17 #1
Share this Question
Share on Google+
8 Replies


ADezii
Expert 5K+
P: 8,624
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:
Expand|Select|Wrap|Line Numbers
  1. 'appExcel Object previously Declared and Instantiated
  2. appExcel.WorksheetFunction.VLookup (arguments)
  3.  
Jul 1 '17 #2

ADezii
Expert 5K+
P: 8,624
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.
Expand|Select|Wrap|Line Numbers
  1. Dim appExcel As Excel.Application
  2. Dim wkb As Excel.Workbook
  3. Dim wks As Excel.Worksheet
  4. Dim lngCtr As Long
  5.  
  6. Set appExcel = New Excel.Application
  7.     appExcel.Visible = True
  8.  
  9. Set wkb = appExcel.Workbooks.Open("C:\Test\Test.xlsx")
  10. Set wks = wkb.Worksheets("Sheet1")
  11.  
  12. With wks
  13.   .Activate
  14.     For lngCtr = 2 To 6   'Cols B to F, Tabs2 thru Tab6
  15.      .Cells(2, lngCtr).Formula = "=If(ISNA(VLOOKUP(B12,Tab" & CStr(lngCtr) & _
  16.                                  "!A1:C300,3,True)),True,False)"
  17.     Next
  18. End With
  19.  
  20. wkb.Save
  21.  
  22. wkb.Close False
  23. Set wkb = Nothing
  24. appExcel.Quit
  25. Set appExcel = Nothing
Jul 1 '17 #3

NeoPa
Expert Mod 15k+
P: 31,419
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.
Jul 1 '17 #4

P: 23
ADezii, and NeoPa thank you for your responses, these have been helpful. This is what I ended up with and it works very well.
Expand|Select|Wrap|Line Numbers
  1. Dim objExcel As Object
  2. Dim objDoc As Object
  3. Dim oExcel As Excel.Application
  4. Dim c As Object
  5. Dim i As Long
  6. Dim DelRange As Range
  7. Dim strExFormula01, strExFormula02, strExFormula03 As String
  8.  
  9. strExFormula01 = "=IF(ISNA(VLOOKUP(RC[-1],_3000!C[-1],1,0)),"""",""Y"")"
  10. strExFormula02 = "=IF(ISNA(VLOOKUP(RC[-2],_4000!C[-2],1,0)),"""",""Y"")"
  11. strExFormula03 = "=IF(ISNA(VLOOKUP(RC[-3],_5000!C[-3],1,0)),"""",""Y"")"
  12.  
  13. Set oExcel = New Excel.Application
  14. Set objExcel = CreateObject("Excel.Application")
  15. 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...
Expand|Select|Wrap|Line Numbers
  1. Application.columns("A:A").Select
  2. .Application.Selection.ColumnWidth = 18
  3. .Application.Range("B:D").Select
  4. .Application.Selection.ColumnWidth = 1.71
  5. .Application.Range("B2:B1100").Formula = strExFormula01
  6. .Application.Range("C2:C1100").Formula = strExFormula02
  7. .Application.Range("D2:D1100").Formula = strExFormula03
  8. .Application.columns("E:E").Select
  9. .Application.Selection.ColumnWidth = 44.78
  10. .Application.Range("F:J").Select
  11. .Application.Selection.ColumnWidth = 8.14
  12. .Application.Range("K:BN").Select
  13. .Application.Selection.ColumnWidth = 10.29
  14. .Application.Selection.numberformat = "mm/dd/yy"
  15. .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.
Jul 7 '17 #5

NeoPa
Expert Mod 15k+
P: 31,419
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.
Jul 7 '17 #6

ADezii
Expert 5K+
P: 8,624
  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:
    Expand|Select|Wrap|Line Numbers
    1. With Application
    2.   .Columns("A:A").Select
    3.    .Selection.ColumnWidth = 18
    4.     .Range("B:D").Select
    5.    .Selection.ColumnWidth = 1.71
    6.     .Range("B2:B1100").Formula = strExFormula01
    7.     .Range("C2:C1100").Formula = strExFormula02
    8.     .Range("D2:D1100").Formula = strExFormula03
    9.   .Columns("E:E").Select
    10.    .Selection.ColumnWidth = 44.78
    11.     .Range("F:J").Select
    12.    .Selection.ColumnWidth = 8.14
    13.     .Range("K:BN").Select
    14.    .Selection.ColumnWidth = 10.29
    15.    .Selection.NumberFormat = "mm/dd/yy"
    16.     .Range("A:BN").Select
    17. End With
Jul 8 '17 #7

NeoPa
Expert Mod 15k+
P: 31,419
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.
Jul 8 '17 #8

ADezii
Expert 5K+
P: 8,624
@NeoPa:
Points well taken and appreciated.
Jul 9 '17 #9

Post your reply

Sign in to post your reply or Sign up for a free account.