Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open a GroupWise .NAB File
Haven't been in this NG for a while. Good to be back!
I'm creating an XL spreadsheet from a .NAB file exported from Novell GroupWise. It's an Address Book group, with names, email addresses, etc exported from GW. I want to open the .NAB, give it the settings to create the spreadsheet, then edit the spreadsheet by deleting some columns and putting in some headers, then saving the file as an XL workbook (.XLS). I recorded a macro first to get the simple stuff down without writing code (very rusty here). So far, it's this: Workbooks.OpenText Filename:= _ "H:\AllDocs\CPI\POIT\Sign-in\POIT Members 2011-09-29.nab", Origin:=-535, _ StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _ , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _ Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _ Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _ 16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), _ Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array( _ 29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), _ Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1), Array(41, 1), Array( _ 42, 1), Array(43, 1), Array(44, 1), Array(45, 1), Array(46, 1), Array(47, 1), Array(48, 1), _ Array(49, 1), Array(50, 1), Array(51, 1), Array(52, 1), Array(53, 1), Array(54, 1), Array( _ 55, 1), Array(56, 1), Array(57, 1), Array(58, 1), Array(59, 1), Array(60, 1), Array(61, 1), _ Array(62, 1), Array(63, 1), Array(64, 1), Array(65, 1), Array(66, 1), Array(67, 1), Array( _ 68, 1), Array(69, 1), Array(70, 1), Array(71, 1), Array(72, 1), Array(73, 1), Array(74, 1), _ Array(75, 1), Array(76, 1), Array(77, 1), Array(78, 1), Array(79, 1), Array(80, 1), Array( _ 81, 1), Array(82, 1), Array(83, 1), Array(84, 1), Array(85, 1), Array(86, 1), Array(87, 1), _ Array(88, 1), Array(89, 1), Array(90, 1), Array(91, 1), Array(92, 1), Array(93, 1), Array( _ 94, 1), Array(95, 1), Array(96, 1), Array(97, 1), Array(98, 1), Array(99, 1), Array(100, 1), _ Array(101, 1), Array(102, 1)), TrailingMinusNumbers:=True Columns("A:B").Select Selection.Delete Shift:=xlToLeft Columns("B:B").Select Selection.Delete Shift:=xlToLeft Columns("C:D").Select Selection.Delete Shift:=xlToLeft Columns("D:D").Select ActiveWindow.SmallScroll ToRight:=2 Columns("D:R").Select Selection.Delete Shift:=xlToLeft Columns("E:S").Select Selection.Delete Shift:=xlToLeft Columns("F:F").Select Selection.Delete Shift:=xlToLeft Columns("G:BT").Select Selection.Delete Shift:=xlToLeft Columns("A:F").Select Columns("A:F").EntireColumn.AutoFit Range("A1").Select ActiveCell.FormulaR1C1 = "First Name" Range("B1").Select ActiveCell.FormulaR1C1 = "Last Name" Range("C1").Select ActiveCell.FormulaR1C1 = "E-Mail Address" Range("D1").Select ActiveCell.FormulaR1C1 = "Office Phone" Range("E1").Select ActiveCell.FormulaR1C1 = "Title" Range("F1").Select ActiveCell.FormulaR1C1 = "Organization" Range("A1:F1").Select Application.CommandBars("Formatting").Visible = True Selection.Font.Bold = True ChDir "H:\AllDocs\CPI\POIT\Sign-in" ActiveWorkbook.SaveAs Filename:= _ "H:\AllDocs\CPI\POIT\Sign-in\POIT Members 2011-09-29.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False End Sub My question is, can I select the file I want to convert, then have the macro take it from there? As it is now, the macro is using the same file every time, and I want to tell the macro which file I want it to operate on. Since XL is immediately processing the .NAB file when I open it, I don't know how to separate the two operations, the selecting of the file, and the importing and conversion that automatically takes place. If this can't be done, my alternative is to open the file and manually give the conversion instructions, then run the macro to format the file, which is doable, but I'm Ben Franklin lazy (hate doing repetitive tasks) and prefer to let XL do as much of the work as possible. Besides, I like learning new things in VBA. Can anyone help me along here? TIA. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I email an excel file in Novel groupwise? | Excel Discussion (Misc queries) | |||
GroupWise integration | Excel Programming | |||
When I open a file, Excel goes to Groupwise Lib | Excel Discussion (Misc queries) | |||
Help with Novell GroupWise. | Excel Programming | |||
VBA send mail with groupwise | Excel Programming |