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

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!
LVL 1
zequestionerAsked:
Who is Participating?
 
TommySzalapskiConnect With a Mentor Commented:
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
 
TommySzalapskiCommented:
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
 
TommySzalapskiCommented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
zequestionerAuthor Commented:
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
 
zequestionerAuthor Commented:
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
 
zequestionerAuthor Commented:
The 'Sort' works perfectly...
0
 
TommySzalapskiCommented:
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
 
zequestionerAuthor Commented:
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
 
TommySzalapskiCommented:
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
 
zequestionerAuthor Commented:
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
 
zequestionerAuthor Commented:
oh ok, i see... i will change it.
0
 
zequestionerAuthor Commented:
Thank you that works better, however it now sorts Row 1 into the sorting instead of leaving it as the header row.
0
 
zequestionerAuthor Commented:
Thanks!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.