S

#### salgud

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").Select

Selection.Delete Shift:=xlToLeft

Columns("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.