[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2013-12-12
13
Medium Priority
?
620 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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 2000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Six Sigma Control Plans

830 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