Solved

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

Posted on 2013-12-12
13
585 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Cygwin - GNU GPL License 1 21
Excel formula Sumif not working 4 26
VBA Works in Excel 2010 Not 2016 Help! 5 17
Merging spreadsheets 8 29
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…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

815 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

8 Experts available now in Live!

Get 1:1 Help Now