Solved Manipulating Excel From Access

[GC2010]

I am curious about the best way to handle this. I am using access vba to open an excel workbook delete a few rows, then delete a few columns, then add some data.
so it's like a step process, each step is dependent upon the first being completed.

For example, I need this

Rows("1:2").Select
Selection.Delete Shift:=xlUp

code to complete before I call this

Rows("2:6").Select
Selection.Delete Shift:=xlUp

And once all of the garbage rows have been deleted, I then need to do further manipulation like capturing the last row etc.

With all of that said, how do I just need to call

wb.Save

throughout the code so that subsequent updates "see" the changes that were made earlier in the code? Or do the changes that are made take affect immediately so there is no need to call wb.Save until the process is completely finished?

 

[Isaac]

They take effect immediately.
Should never use Select, Selection, Active, or Activate in Excel VBA code. Sooner or later it will prove unreliable and possibly fail and be very hard to troubleshoot. Declare variables, set or assign them the appropriate values, and act on them..don't depend on things be selected and activated.

 

[GC2010]

how would you advise I re-write that to not use select?

 

[Ranman256]

use normal xl commands inside the with XL block.
dont forget to use the dot in the front of the command:

dim XL As Excel.Application
Set XL = CreateObject("excel.application")
XL.Visible = true
with xl
  .Workbooks.Open sFilename
  .Rows("1:2").Select

     'do stuff here

   .activeworkbook.save
.quit
end sub
set xl = nothing

 

[GC2010]

but we are still using Select in the above instance tho?

 

[Isaac]

change things like this

Rows("1:2").Select
Selection.Delete Shift:=xlUp

to

Rows("1:2").Delete Shift:=xlUp

 

[GC2010]

I was also using wb.Sheets(1).Select

How should I change that?

 

[Isaac]

depends on the rest of your code..

 

[sxschech]

some random examples: delete, copy, paste, add a formula, specific cell using Row number and Col Number rather than LetterNumber:

.Rows(2).Delete
.Range("F2:G" & lastRow + 1).Copy
.Range("S2").PasteSpecial xlPasteAll
.Application.CutCopyMode = False
.Range("I2:I" & lastRow + 1).Formula = "=IF(AND(F2=S2,G2=T2)=TRUE,0,1)"
.Cells(3, 1).CopyFromRecordset rs

 

[GC2010]

This is what I have

Function FormatExcel()
    Dim wb As Excel.Workbook
    Dim xlApp As Excel.Application
    Dim ws As Object

    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = False

    Set wb = xlApp.Workbooks.Open("C:\Test\EmployeeData.xlsx", False, False)
    Set ws = wb.Sheets(1)

    wb.Sheets(1).Select

    Rows("1:2").Delete Shift:=xlUp
    Rows("2:6").Delete Shift:=xlUp
    Rows("2:2").Delete Shift:=xlUp

    wb.Sheets(1).Columns("B:H").EntireColumn.Delete

    Range("A1").Value = "Employee Number"

    wb.Save
    wb.Close
    xlApp.DisplayAlerts = False
    xlApp.Quit

End Function

 

[Isaac]

Hey just out of curiousity, can you let me know whether or not when you type (in the middle of your function), for example, xlApp. after you press the Dot, do you get Intellisense pop ups?

 

[Isaac]

Is there any particular reason you have this as a Function rather than a Sub? Normally you would use a Sub to "do things", and a Function only if you needed to return a value. I am sure this statement can be scrutinized for exceptions, but that's the normal approach.

 

[GC2010]

yes, I get intellisense.

I am sure this is bad practice, but I declare it as a function when I am testing and still working on it, then set it as a sub when I have verified the code works as expected.

 

[Isaac]

I am sure this is bad practice, but I declare it as a function when I am testing and still working on it, then set it as a sub when I have verified the code works as expected.

I am not sure it's a bad practice, I was just curious.
Here is my suggestion for your code. Just a suggestions.

Function FormatExcel()
    Dim wb As Excel.Workbook
    Dim xlApp As Excel.Application
    Dim ws As Object

    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = False

    Set wb = xlApp.Workbooks.Open("C:\Test\EmployeeData.xlsx", False, False)
    Set ws = wb.Sheets(1)

    'wb.Sheets(1).Select
    'I would comment out the above line

    'i would add the worksheet qualifier below
    ws.Rows("1:2").Delete Shift:=xlUp
    ws.Rows("2:6").Delete Shift:=xlUp
    ws.Rows("2:2").Delete Shift:=xlUp

    wb.Sheets(1).Columns("B:H").EntireColumn.Delete

    'i would add the worksheet qualifier below'
    ws.Range("A1").Value = "Employee Number"

    wb.Save
    wb.Close
    xlApp.DisplayAlerts = False
    xlApp.Quit

End Function

These may seem trivial or picky, but I will never forget the day when, after programming in Excel for a year or two, my programs - especially the ones with a lot going on and triggered by users, who also had various other Excel files open they would work on at the same time - started to experience ALL types of errors. I would troubleshoot them one by one and none of them seemed to have a direct cause. Lots of "subscript out of range", "Invalid argument", and just everything from A to Z. Finally someone looked at my code....not qualified fully, and using select/activate, and gave me those valuable tips. The problem took a while to rear its ugly head but then I was glad I changed my ways ha ha

 

[GC2010]

Thanks for that advice. I've been dabbling with VBA for quite a few years, but never done anything too advanced/outside the macro recorder.

Is there something I'm doing that is causing this code to sometimes leave excel open in the Task Manager/Details

    wb.Close
    xlApp.DisplayAlerts = False
    xlApp.Quit

 

[Isaac]

The application's Quit command should be pretty foolproof, I can't think of any reason why it wouldn't work. I've never seen it not work, really.

Of course as you probably already are aware this function should have an error handler (as any non-trivial procedure should). Inside the error handler, you would clean things up...including quitting any application instances that haven't already been quit. Not sure if errors are occurring and you're troubleshooting them but then the app instance is still open.

 

[cheekybuddha]

>> Normally you would use a Sub to "do things", and a Function only if you needed to return a value. <<

I think this is a bit dogmatic.

If you want to get code to run from a control's onclick property, for example, you can use an expression that refers to a function. eg:

=DoClickStuff()

avoiding the need for another code stub in the form's module. DoClickStuff() might just 'do stuff' and not return a value, but you can only do this using a function. A sub won't work in this context.

I also think it's helpful to use a function and return a boolean or numeric result to indicate the success/status of the function. For instance: you could have a function that inserts a record:

Function InsertValue(strVal As String) As Boolean

  If Len(strVal) Then
    With CurrentDb
      .Execute "INSERT INTO YourTable (Fld) VALUES ('" & strVal & "');", dbFailOnError
      InsertValue = .RecordsAffected > 0
    End With
  End If

End Function

Now I have the choice of calling it simply like a sub to insert a value:

InsertValue "Hello"

Or I can use it to test the outcome:

  If InsertValue(Me.txtVal & vbNullString) Then
    Debug.Print "Me.txtVal contained a value"
  Else
    Debug.Print "Me.txtVal was Null or ZLS"
  End If

In fact, I don't think I have written a sub in over fifteen years!!

WRT, the code, I think you can also change this line:

' ...
    wb.Sheets(1).Columns("B:H").EntireColumn.Delete
' ...

to:

' ...
    ws.Columns("B:H").EntireColumn.Delete
' ...

 

[cheekybuddha]

>> Is there something I'm doing that is causing this code to sometimes leave excel open in the Task Manager/Details <<

Yes, if you find orphaned instances of Excel in Task Manager you can be 99.999999% certain that you have an 'unqualified reference' to an Excel object in your automation code.

 

[Isaac]

Yes, I suppose it is a bit dogmatic. I don't disagree with anything you've written, but I've been in the habit of writing Functions only when I actually needed to do one of those types of things. I've not been in the habit of using that =Function() method in an event wire-er, (just personal preference I guess).
But I mean we're talking about someone brand new to VBA... to me it makes more sense to learn the generic thing that I said, and with time learn everything you said - that's why I mentioned that. But everything you said is valuable and true.

 

[isladogs]

Agree with @cheekybuddha's comments in post #17
Also, if you wish to refer to a procedure in a macro such as autoexec, it has to be a function rather than a sub,

As functions can do everything that a sub can do ...and return a value, there are developers who only ever write code as functions