Solved

VB script or macro to sort a Column & add a table in Excel sheet

Posted on 2013-12-12
13
568 Views
Last Modified: 2013-12-12
Can someone provide an example vb script or macro which can perform the following:

(via cscript)

1. Open file.xlsx
2. Set Column B to 'Sort Ascending A-Z'
3. Insert a table from 'Cell A-1 through Cell J-681'
4. Save file.xlsx
5. Close Excel.

The Cell range for the table needs to be able to edit if needed...

Thanks experts!
0
Comment
Question by:zequestioner
  • 8
  • 5
13 Comments
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 39714785
What should happen to column B when the table is inserted? Should it shift to column L?
What do you mean by "insert table"? Just add blank space?
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 39714793
If my assumptions are true, then this should work

Set xl = CreateObject("Excel.application")

xl.Application.Workbooks.Open "C:\temp\junk.xls"
set book = xl.Application.Workbooks("junk.xls")
xl.Application.Visible = True
Set sheet = book.Worksheets(1)
' Set this as the range you want
sheet.Range("A:B").Sort(sheet.Range("B1"))
sheet.Range("A:J").Insert 'Change this to whatever you want the range to be
book.Save
xl.Application.Quit
Set xl = Nothing

Open in new window

0
 
LVL 1

Author Comment

by:zequestioner
ID: 39714846
I mean insert a table as if you opened the Excel file, then clicked on the cells mentioned, (shift-select so they are all highlighted) then click Insert, then Table. Then a popup appears which says something like 'Where is the data for your table?' =$B$1:$I$29 for example,

then there is a checkbox that says 'my table has headers' then I click OK...

that creates a table for me and I am trying to do this automatically...

Sorry for the confusion.. hope this helps.
0
 
LVL 1

Author Comment

by:zequestioner
ID: 39714851
sorry... yes the code you provided adds blank cells in columns A-J...

I want to make the data inside A-J become a 'Table'... Row 1 are the column names for the table..

Sorry, i just want to insert a table just as it would happen manually..
0
 
LVL 1

Author Comment

by:zequestioner
ID: 39714855
The 'Sort' works perfectly...
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 39714858
Set xl = CreateObject("Excel.application")

xl.Application.Workbooks.Open "C:\temp\junk.xls"
set book = xl.Application.Workbooks("junk.xls")
xl.Application.Visible = True
Set sheet = book.Worksheets(1)
' Set this as the range you want
sheet.Range("A:B").Sort(sheet.Range("B1"))
sheet.ListObjects.Add 1, sheet.Range("$A$1:$J$681"), 0, 1 'Change the last 1 to 0 for no headers
book.Save
xl.Application.Quit

Open in new window

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 1

Author Comment

by:zequestioner
ID: 39714971
Thanks! The table insert is working great....

The only thing i noticed about the sorting... when I sort Column C ascending a-z the other column data does not maintain consistency with the newly ascending alphabetically sorted column C...

So when I use the sort function, it messes up the data..

Can you see what I"m saying?
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 39714986
Yes. It's because I set the sort range as "A:B" in the sample code.
Set that to whatever you want it to be. I'm guessing "A:J"?
sheet.Range("A:J").Sort(sheet.Range("B1"))
0
 
LVL 1

Author Comment

by:zequestioner
ID: 39714996
So for example,

If Coulmn C and D look like this:

C                    D
Your App        v3.2
My App           v1.0

After I use the sorting function, it will correctly sort Column C alphabetically... but when you sort manually inside the app, the data stays consistent... when I sort using the vb code, data only changes in column C and the row data does not maintain. So in the example above, the vb code will make sort My App above Your App and My app will now show v3.2 instead of v1.0.

Do I explain this correctly? please let me know if there is further explanation.
0
 
LVL 1

Author Comment

by:zequestioner
ID: 39715000
oh ok, i see... i will change it.
0
 
LVL 1

Author Comment

by:zequestioner
ID: 39715025
Thank you that works better, however it now sorts Row 1 into the sorting instead of leaving it as the header row.
0
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 500 total points
ID: 39715081
Ah yes. You have to tell it to expect the header.
Use this for the sort line
sheet.Range("A:J").Sort sheet.Range("B1"), , , , , , , 1

The , , , ,  stuff just means that those argument are left to default values. The Sort function takes three keys and sort orders and a type, but the 8th argument is 1 for a header row and 0 for no header
0
 
LVL 1

Author Comment

by:zequestioner
ID: 39715183
Thanks!!!
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

A short article about problems I had with the new location API and permissions in Marshmallow
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now