Export query result to excel with column width and some more changes...VBA? (1 Viewer)

A1ex037

Registered User.
Local time
Today, 23:14
Joined
Feb 10, 2018
Messages
50
Well, my problem is that I have to visually mark it, otherwise the responsible person might miss it in the warehouse (happens very often). I am very thankful for your help, I am already on "highlight" thing, looking how to do it, and I will get back here soon with the progress report.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:14
Joined
Feb 19, 2002
Messages
42,976
But is there another hidden agenda for using a SELECT CASE in that context,
It is a template I use for error traps. It just makes it easier to just pop in an additional code without restructuring the procedure. Sometimes when I'm writing code I don't know what errors I might expect. As the code gets tested, I add the specific error codes as they pop up. Sometimes I will ignore the errors and other times I will display an error message or do some extra processing. To make that easy, I display the code as well as the description in the "else" case. Otherwise, I have to look up the description to find the code that I need to trap.

@A1Ex37,
Since I am not an Excel expert, I frequently find myself unable to figure out how to do things. When that happens, I load the data I want to format into a new Excel workbook and turn on the macro recorder. Then I try to perform the action using the Excel ribbon. Once I finish, I stop the macro recorder and copy the code into Access. Frequently, the code just ports as written but in some cases you will need to change how things are referenced because when the macro recorder writes code it assumes it will run in Excel but when the code runs from Access, how you reference objects can be different but what you do is common between the two environments.
 

Isaac

Lifelong Learner
Local time
Today, 15:14
Joined
Mar 14, 2017
Messages
8,738
Almost never should you actually deploy code written by the macro recorder, unchanged. It relies on Select, Selection, Active, and Activate.
 

A1ex037

Registered User.
Local time
Today, 23:14
Joined
Feb 10, 2018
Messages
50
I did manage to get it done with this:

Code:
LR = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
i = 1

Do While i <= LR

If Cells(i, 13).Value = "FALSE" Then
ws.Rows(i).EntireRow.Interior.Color = RGB(255, 0, 0)
End If

i = i + 1

Loop

ws.Columns(13).Delete

MsgBox "S&R finished", vbInformation + vbOKOnly, "S&R finished"

13-th was the TRUE/FALSE column that gets deleted at the end. I still have to figure out how to color first 12 columns (now the entire row gets colored). It gets the job done, but having a bit more control over it would be great.
 

Isaac

Lifelong Learner
Local time
Today, 15:14
Joined
Mar 14, 2017
Messages
8,738
Great progress! Glad to hear it.

Maybe adjust one line like:
ws.cells(i,2).Interior.Color = RGB(255, 0, 0)

...(if you wanted to only color the cell in column B)
 

Users who are viewing this thread

Top Bottom