Open a GroupWise .NAB File

S

salgud

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

Gord

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
 
S

salgud

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
Click to expand...


Thanks for your reply. Unfortunately, that won't work. When the .NAB file
is opened by XL, it automatically starts the conversion process to make it
into an XL file. So the macro can't even be started until that is
completed. Once that's completed, the first part of the macro, which sets
the conversion settings normally done manually, is not needed, and if left
in, trashes the file. I need some way to intervene, (maybe some event
driven code?) and take over the process. Maybe it looks at the file name,
and if it sees the .NAB extension, offers to take over, or detects the file
import routine and offers to take over. But I have never tried to do
anything like that, and don't have a clue where to start.
 
M

Martin Brown

Thanks for your reply. Unfortunately, that won't work. When the .NAB file
is opened by XL, it automatically starts the conversion process to make it
into an XL file. So the macro can't even be started until that is
completed. Once that's completed, the first part of the macro, which sets
the conversion settings normally done manually, is not needed, and if left
in, trashes the file. I need some way to intervene, (maybe some event
driven code?) and take over the process. Maybe it looks at the file name,
and if it sees the .NAB extension, offers to take over, or detects the file
import routine and offers to take over. But I have never tried to do
anything like that, and don't have a clue where to start.
Click to expand...

Use Application.GetOpenFileName instead and pass the resulting filename
chosen by the user to your subroutine.

Regards,
Martin Brown
 
S

salgud

Use Application.GetOpenFileName instead and pass the resulting filename
chosen by the user to your subroutine.

Regards,
Martin Brown
Click to expand...

Thanks for your reply. Maybe I'm not making myself clear here, because I
think this approach has the same problem. When I open the .NAB file, XL
_automatically_ begins the file conversion routine, first asking what type
of delimiter is being used, then continues with 2 other dialog boxes to
fill to define how to convert the file. I can't start, or stop, a macro
once this process begins until it is done. So I can't open the .NAB file,
then do anything except fill in the blanks telling XL how to do the
conversion. So I can't run the macro to pass the filename until that is
done. I can still run the routine to clean up the columns and create the
column headers afterwards, but I can't automate the conversion process
settings unless I can intervene when the .NAB file is opened and before the
auto conversion process begins. Does that make it any clearer? Does anyone
have any suggestions, or is this just not doable?
 
M

Martin Brown

Thanks for your reply. Maybe I'm not making myself clear here, because I
think this approach has the same problem. When I open the .NAB file, XL
_automatically_ begins the file conversion routine, first asking what type
of delimiter is being used, then continues with 2 other dialog boxes to
fill to define how to convert the file. I can't start, or stop, a macro
once this process begins until it is done. So I can't open the .NAB file,
then do anything except fill in the blanks telling XL how to do the
conversion. So I can't run the macro to pass the filename until that is
done. I can still run the routine to clean up the columns and create the
column headers afterwards, but I can't automate the conversion process
settings unless I can intervene when the .NAB file is opened and before the
auto conversion process begins. Does that make it any clearer? Does anyone
have any suggestions, or is this just not doable?
Click to expand...

I have no idea about the internal structure of a .NAB file so this may
or may not be helpful. The way that I open structured text files with
VBA in Excel is

Workbooks.OpenText filename:=filename, _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, Comma:=True, Space:=False, Other:=False

Obviously you have to set the various parameters to whatever your
strange file requires and the conversion then proceeds automatically.

Regards,
Martin Brown
 
S

salgud

I have no idea about the internal structure of a .NAB file so this may
or may not be helpful. The way that I open structured text files with
VBA in Excel is

Workbooks.OpenText filename:=filename, _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, Comma:=True, Space:=False, Other:=False

Obviously you have to set the various parameters to whatever your
strange file requires and the conversion then proceeds automatically.

Regards,
Martin Brown
Click to expand...

Thanks, Martin! I can't wait to try this - I'm pretty sure just looking at
it that this is what I am looking for. Will post after I get a chance to
test it.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Forcing xl2K7 Column Count back to xl2K3 size 3
Permutations andText to column problem 6
Which Excel event to call when opening a .txt file 1
Copy part of text file 4
Open delimited file 3
Complie Syntax Error 6
Column is overwritten 4
Loop is slow trying array code 17

Top