Welcome to Dynamics Life Blog by Igor Beeone!
Good day guys,
here goes a guide how to manipulate ZIP archives using windows functionality. Here will be described technique for zipping and unzipping functionality. Functionality perfectly works on XP/2003/Vista machines. (Haven't tested on 2000 system). Year ago I was writing on mibuso this thing but maybe somebody would like to see complete article on this :)
As most of you know, Zip folders functionality appeared from WindowsXP was launched. So now we can use this feature in our Navision projects. For example client want to export any data from navision, ZIP it and send somewhere. So we will discuss second step: Zip it :)
So let's start by creating needed variables for this functionality (there are no much code to play with it:)):
Shell32 Automation 'Microsoft Shell Controls And Automation'.Shell - Shell AutomationZIPFolder Automation 'Microsoft Shell Controls And Automation'.Folder - It will be our Zip FolderZipFile File - Zip FileFilename Text 250 - filename of a file to be transferredIntVal Integer - integer value (discussed later)ZipFileName Text 250 - zip file filenamei Integer- counter
Now let's start writing code:)
//Code //>>CREATING ZIP FILE ZipFile.CREATE('c:\myzipfolder.zip'); //Zip file createdZipFile.TEXTMODE(FALSE); // in binary modeIntVal:=101010256; //here we input zip header (it's 6054B50)ZipFile.WRITE(IntVal); //it's written hereIntVal:=0; //now we input next zip file information for more info refer to//http://www.pkware.com/documents/casestudies/APPNOTE.TXTFOR i:=1 TO 9 DO ZipFile.WRITE(IntVal); // 9 zeros in binary format - int32 typeZipFile.CLOSE; // ok, proper zip file is preparedCREATE(Shell32); // now we create shell automationZIPFolder:=Shell32.NameSpace('c:\myzipfolder.zip'); //opening our zip file as a folder :)Filename := 'c:\testfile.txt'; // this file will be compressed into myzipfolder.zip archive ZIPFolder.CopyHere(Filename); // just copying :) nice huh?CLEAR(Shell32); //vuala:)
Same way you may extract files from zip files. Hope you like it :) Object is in attachment.
Br, Igor Beeone
Hello,here goes next part of Excel reader using ADO. Now we will create Two functions identical by result to native functions from Navision 370 table (Excel Buffer).Two functions were created:SelectSheetName(filename);
Function reads Excel schema and shows up dialog with sheet names to select. Let's look at this code (SelectSheetName):
Now we must reorganize previous article function to read data to Excel Buffer (ReadSheet(filename,sheetname)):
So as You can see it's very simple to implement and it works very fast! Try for Your own.Full source code is provided here: http://igor.beeone.googlepages.com/DLJET2a.zip
Now You may schedule Navision database (Database File/Native connection/SQL connection) backup in 5 minutes. Easy-to-use, flexible solution contains next features:1. Support for SQL Server/Native Server/Database types!2. Flexible Backup Name generation using predefined constants!3. Automatic Companies retrieving functionality, which helps reduce mistakes while inputing Company names!4. All the Navision Backup environment options exists!5. Supports for 7Zip and ZIP archive formats to reduce backup size up to 90%!6. Flexible E-Mail notification (On Success/On Error/Both) with log attachment!7. Log file creation!8. Not linked to selected Navision environment language!9. Supports 3.X-5.X Navision versions10. Easy-to-use interface. Use standard Windows Scheduler!11. Possibility to run Script/Executable after Backup process (archive/backup arguments support!)12. NT/Database identification support Latest downloads could be obtained on MiBuSo or http://www.dynamicsway.com website.Hope to hear comments from You.For more information visit http://www.mibuso.com/dlinfo.asp?FileID=879 website.Screenshots: www.dynamicsway.com/Screens.htmlWith Respect to DynamicsUsers community.
here is the first tip and first post of mine in this blog for reading any data file/database using ADO and jet drivers. You can find proper connection strings on http://www.connectionstrings.com/
As an example we will discover how to read Excel file using Jet driver. It's not as flexible as using direct Excel Automation (using Automation you can access field properties and so on), but if You would like to read plain data - it's much faster solution and easy to implement.
Let's look at code:
define next variables:
Now let's discover code in a codeunit (OnRun trigger):
//Here we must define a filename to read from1: filename:='c:\1.xls';
//Here we must define sheetname of excel file to read
//Now we are creating Connection automation3: CREATE(Connection);
//Here we define a connection string: in our case it‘s a filename as Data Source4: Connection.ConnectionString:='Data Source='+filename; //Define provider (in out case it‘s Jet.OLEDB driver), and additional properties related to Excel//option (using version 8.0), HDR - means is first row// a header to skip it, if your excel file has a header in a first row - use this option to skip it// "IMEX=1;" tells the driver to always read data as text.5: Connection.Provider:='Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=No;IMEX=1";'; //Opening the connection6: Connection.Open();
//Creating Recordset variable7: CREATE(Recordset);
//Opening connection and read data using SELECT * FROM SheetX way, so we are reading all the// data from the sheet8: Recordset.Open(STRSUBSTNO(SELECTSTRING,sheetname),Connection,1,1);
//Now we will start reading Excel file using a loop//Loop started//while not end of recordset - read data9: WHILE NOT Recordset.EOF DO BEGIN 10: Fields:=Recordset.Fields;
//Now we have row data in Fields variable11: MESSAGE(FORMAT(Fields.Item(i).Value));
//Output Column (i) value in a message box- where i variable is an integer12: Recordset.MoveNext;
//Move to the next row in a recordset13:END;
//Loop finished//Stop Reading Excel file//Closing Recordset14: Recordset.Close;
//Clearing Recordset variable15: CLEAR(Recordset);//Closing Connection16: Connection.Close();//Clearing Connection automation variable17: CLEAR(Connection);