Solved

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

Posted on 2013-12-12
13
579 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

920 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

12 Experts available now in Live!

Get 1:1 Help Now