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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open a GroupWise .NAB File
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open a GroupWise .NAB File
On Thu, 29 Sep 2011 12:44:01 -0700, Gord wrote:
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 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open a GroupWise .NAB File
On 29/09/2011 21:24, salgud wrote:
On Thu, 29 Sep 2011 12:44:01 -0700, Gord wrote: 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 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. Use Application.GetOpenFileName instead and pass the resulting filename chosen by the user to your subroutine. Regards, Martin Brown |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open a GroupWise .NAB File
On Thu, 29 Sep 2011 22:06:23 +0100, Martin Brown wrote:
On 29/09/2011 21:24, salgud wrote: On Thu, 29 Sep 2011 12:44:01 -0700, Gord wrote: 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 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. Use Application.GetOpenFileName instead and pass the resulting filename chosen by the user to your subroutine. Regards, 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open a GroupWise .NAB File
On 29/09/2011 23:22, salgud wrote:
On Thu, 29 Sep 2011 22:06:23 +0100, Martin Brown wrote: On 29/09/2011 21:24, salgud wrote: On Thu, 29 Sep 2011 12:44:01 -0700, Gord wrote: 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 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. Use Application.GetOpenFileName instead and pass the resulting filename chosen by the user to your subroutine. Regards, 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? 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open a GroupWise .NAB File
On Fri, 30 Sep 2011 10:31:23 +0100, Martin Brown wrote:
Use Application.GetOpenFileName instead and pass the resulting filename chosen by the user to your subroutine. Regards, 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? 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 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. |
Reply |
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 |