Solved Manipulating Excel From Access (1 Viewer)

GC2010

Registered User.
Local time
Today, 13:00
Joined
Jun 3, 2019
Messages
120
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
Code:
Rows("1:2").Select
Selection.Delete Shift:=xlUp

code to complete before I call this
Code:
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
Code:
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

Lifelong Learner
Local time
Today, 13:00
Joined
Mar 14, 2017
Messages
8,738
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

Registered User.
Local time
Today, 13:00
Joined
Jun 3, 2019
Messages
120
how would you advise I re-write that to not use select?
 

Ranman256

Well-known member
Local time
Today, 16:00
Joined
Apr 9, 2015
Messages
4,339
use normal xl commands inside the with XL block.
dont forget to use the dot in the front of the command:

Code:
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

Registered User.
Local time
Today, 13:00
Joined
Jun 3, 2019
Messages
120
but we are still using Select in the above instance tho?
 

Isaac

Lifelong Learner
Local time
Today, 13:00
Joined
Mar 14, 2017
Messages
8,738
change things like this
Code:
Rows("1:2").Select
Selection.Delete Shift:=xlUp
to
Code:
Rows("1:2").Delete Shift:=xlUp
 

GC2010

Registered User.
Local time
Today, 13:00
Joined
Jun 3, 2019
Messages
120
I was also using wb.Sheets(1).Select

How should I change that?
 

Isaac

Lifelong Learner
Local time
Today, 13:00
Joined
Mar 14, 2017
Messages
8,738
depends on the rest of your code..
 

sxschech

Registered User.
Local time
Today, 13:00
Joined
Mar 2, 2010
Messages
791
some random examples: delete, copy, paste, add a formula, specific cell using Row number and Col Number rather than LetterNumber:
Code:
.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

Registered User.
Local time
Today, 13:00
Joined
Jun 3, 2019
Messages
120
This is what I have

Code:
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

Lifelong Learner
Local time
Today, 13:00
Joined
Mar 14, 2017
Messages
8,738
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

Lifelong Learner
Local time
Today, 13:00
Joined
Mar 14, 2017
Messages
8,738
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

Registered User.
Local time
Today, 13:00
Joined
Jun 3, 2019
Messages
120
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

Lifelong Learner
Local time
Today, 13:00
Joined
Mar 14, 2017
Messages
8,738
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.
Code:
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

Registered User.
Local time
Today, 13:00
Joined
Jun 3, 2019
Messages
120
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
Code:
    wb.Close
    xlApp.DisplayAlerts = False
    xlApp.Quit
 

Isaac

Lifelong Learner
Local time
Today, 13:00
Joined
Mar 14, 2017
Messages
8,738
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

AWF VIP
Local time
Today, 20:00
Joined
Jul 21, 2014
Messages
2,237
>> 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:
Code:
=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:
Code:
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:
Code:
InsertValue "Hello"
Or I can use it to test the outcome:
Code:
  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:
Code:
' ...
    wb.Sheets(1).Columns("B:H").EntireColumn.Delete
' ...
to:
Code:
' ...
    ws.Columns("B:H").EntireColumn.Delete
' ...
 

cheekybuddha

AWF VIP
Local time
Today, 20:00
Joined
Jul 21, 2014
Messages
2,237
>> 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

Lifelong Learner
Local time
Today, 13:00
Joined
Mar 14, 2017
Messages
8,738
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

MVP / VIP
Local time
Today, 20:00
Joined
Jan 14, 2017
Messages
18,186
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
 

Users who are viewing this thread

Top Bottom