Have you tried popping up the fileopen dialog box?

Application.Dialogs(xlDialogOpen).Show

Pick the *.nab file you want to open and your code does the rest.

As far as naming the resultant file..........that's another story<g


Gord Dibben Microsoft Excel MVP


On Thu, 29 Sep 2011 12:52:36 -0600, salgud
wrote:

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.