[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 969
  • Last Modified:

VBScript: Open ZIP>Extract ZIP from Within Lotus Notes

Hello Experts.

    What I'm needing to do is fairly simple (on paper), however, script wise I'm not sure.  I receive a .ZIP file everyday in my Lotus Notes Inbox.  This .ZIP file is nothing more than a .CSV report.   What I'd like the script to do is open the ZIP attachment from within My Lotus email.  The subject line for this report I receive is always the same, so perhaps we could filter on that so it knows exactly what emails to act on.  

     Once the script is able to extract the .CSV from the ZIP, I'd like to then remove ALL COLUMNS in that .CSV file,  but the following:

1.)  Event TimeStamp (UTC)
2.)  Network Node Address
3.)  Target User
4.)  Source Computer

    It would drop the newly formatted .CSV file on my desktop.  Finally, it would then do a "Sort By A to Z" function on column "Target User"

The end result output would look like this:
Sample Output

0
itsmevic
Asked:
itsmevic
  • 17
  • 14
1 Solution
 
Sjef BosmanGroupware ConsultantCommented:
Well, you can write some code that
- saves the attachment
- calls a batch file (using the Shell command) to extract the CSV from the ZIP file
- or use Shell directly to call the Unzip executable

You can open Excel from within Lotus Notes and handle the rest there.
0
 
RobSampsonCommented:
Hi, here's some code that will go through your Inbox for a document containing the strSubjectToFind text, and detach each attachment.

Which zip program do you have available to unzip it?

Regards,

Rob.
strPassWd = InputBox("Enter your Lotus Notes password:", "Lots Notes Password")
strFolderName = "($Inbox)"
strSubjectToFind = "Consistent Subject of Message"
strTargetAttachmentFolder = "C:\Temp\"

If Right(strTargetAttachmentFolder, 1) <> "\" Then strTargetAttachmentFolder = strTargetAttachmentFolder & "\"
' Initialize Notes
Set objSession = CreateObject("Lotus.NotesSession")
Call objSession.Initialize(strPassWd)
' Open current user's mail file.
strServer = objSession.GetEnvironmentString("MailServer", True)
strFile = objSession.GetEnvironmentString("MailFile", True)
Set objDB = objSession.GetDatabase(strServer, strFile, False)
' Open and read the specified folder.
On Error Resume Next
Set objFolder = objDB.GetView(strFolderName)
If (objFolder Is Nothing) Then
	MsgBox "Error attaching to folder " & strFolderName
ElseIf (objFolder.AllEntries.Count = 0) Then
	MsgBox strFolderName & " contains no messages."
Else
	Err.Clear
	On Error GoTo 0
	Set objDoc = objFolder.GetFirstDocument
	WScript.Echo "Messages in " & strFolderName
	Do Until (objDoc Is Nothing)
		strSubject = objDoc.GetItemValue("Subject")(0)
		If InStr(strSubject, strSubjectToFind) > 0 Then
			strBody = Replace(objDoc.GetFirstItem("Body").Text, vbCrLf, vbLf)
			WScript.Echo strSubject
			For Each objFile In objDoc.GetFirstItem("Body").EmbeddedObjects
				WScript.Echo objFile.Name
				Call objFile.ExtractFile(strTargetAttachmentFolder & objFile.Name)
			Next
		End If
		Set objDoc = objFolder.GetNextDocument(objDoc)
	Loop
End If

WScript.Echo "Finished"

Open in new window

0
 
itsmevicAuthor Commented:
Hey there Rob! Great to hear from you again!  I'll get that .ZIP program info for you today.  
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
itsmevicAuthor Commented:
To answer your question Rob.  I have two methods of extracting .ZIP files on my system.  One, I'll use the extraction method that is built in with the Windows 7 OS OR if I'm feeling like a party animal that day, I'll use WINZIP v14   lol!  They both work well.  : )    
0
 
itsmevicAuthor Commented:
Yep that worked Rob, it extracts the ZIP file from Notes inbox perfectly and places the ZIP file on my desktop. Very cool!   I had to bit of research, but for some reason if I do not run that script above from the C:\Windows\SysWOW64 directory it would error out.  I assume this has something to do with my 64-bit system I'm on.  Anyway, the extraction process works great!  Thank you!  

Now that we are able to extract the ZIP from Notes Email, I suppose our next order of business is extracting the .CSV file from that .ZIP file and then killing all columns in that .CSV EXCEPT the one's mentioned above, and then finally doing a "Sort By A to Z" on the "Target User column.  The the newly extracted, newly formatted .CSV output file could be placed on my desktop along with with ZIP file that was extracted.  Sound doable?  
0
 
RobSampsonCommented:
Yes, it is achievable, certainly.  I will provide inbuilt decompression code, because I already have that.

I'll see how I go.  Shouldn't be too hard....

Rob.
0
 
itsmevicAuthor Commented:
Awesome! Thank you Rob!
0
 
RobSampsonCommented:
Hi, give this a shot.  It will detach and extract to your desktop, then open *any* CSV file in the ZIP file, and perform the required actions.

Regards,

Rob.
Set objShell = CreateObject("WScript.Shell")

strPassWd = InputBox("Enter your Lotus Notes password:", "Lots Notes Password")
strFolderName = "($Inbox)"
strSubjectToFind = "Consistent Subject of Message"
strTargetAttachmentFolder = objShell.SpecialFolders("Desktop")

Const xlToLeft = -4159
Const xlSortOnValues = 0
Const xlAscending = 1
Const xlSortNormal = 0
Const xlUp = -4162
Const xlNo = 2
Const xlYes = 1
Const xlTopToBottom = 1
Const xlPinYin = 1

If Right(strTargetAttachmentFolder, 1) <> "\" Then strTargetAttachmentFolder = strTargetAttachmentFolder & "\"
' Initialize Notes
Set objSession = CreateObject("Lotus.NotesSession")
Call objSession.Initialize(strPassWd)
' Open current user's mail file.
strServer = objSession.GetEnvironmentString("MailServer", True)
strFile = objSession.GetEnvironmentString("MailFile", True)
Set objDB = objSession.GetDatabase(strServer, strFile, False)
' Open and read the specified folder.
On Error Resume Next
Set objFolder = objDB.GetView(strFolderName)
If (objFolder Is Nothing) Then
	MsgBox "Error attaching to folder " & strFolderName
ElseIf (objFolder.AllEntries.Count = 0) Then
	MsgBox strFolderName & " contains no messages."
Else
	Err.Clear
	On Error GoTo 0
	strDetachedFiles = ""
	Set objDoc = objFolder.GetFirstDocument
	WScript.Echo "Messages in " & strFolderName
	Do Until (objDoc Is Nothing)
		strSubject = objDoc.GetItemValue("Subject")(0)
		If InStr(strSubject, strSubjectToFind) > 0 Then
			strBody = Replace(objDoc.GetFirstItem("Body").Text, vbCrLf, vbLf)
			WScript.Echo strSubject
			For Each objFile In objDoc.GetFirstItem("Body").EmbeddedObjects
				WScript.Echo objFile.Name
				Call objFile.ExtractFile(strTargetAttachmentFolder & objFile.Name)
				If strDetachedFiles = "" Then
					strDetachedFiles = strTargetAttachmentFolder & objFile.Name
				Else
					strDetachedFiles = strDetachedFiles & "|" & strTargetAttachmentFolder & objFile.Name
				End If
			Next
		End If
		Set objDoc = objFolder.GetNextDocument(objDoc)
	Loop
End If

If strDetachedFiles = "" Then
	WScript.Echo "No files were detached."
Else
	For Each strFilePath In Split(strDetachedFiles, "|")
		If Right(LCase(strFilePath), 4) = ".zip" Then
			strExtractedFiles = ExtractZip(strFilePath, strTargetAttachmentFolder)
			For Each strExtractedFile In Split(strExtractedFiles, "|")
				If Right(LCase(strExtractedFile), 4) = ".csv" Then
					Set objExcel = CreateObject("Excel.Application")
					objExcel.Visible = True
					Set objWB = objExcel.Workbooks.Open(strExtractedFile, False, False)
					Set objSheet = objWB.Sheets(1)
					For intCol = objSheet.Cells(1, 256).End(xlToLeft).Column To 1 Step -1
						strCol = objSheet.Cells(1, intCol).Value
						If strCol <> "Event TimeStamp (UTC)" And strCol <> "Network Node Address" And strCol <> "Target User" And strCol <> "Source Computer" Then
							objSheet.Columns(Chr(intCol + 64) & ":" & Chr(intCol + 64)).Delete xlToLeft
						End If
					Next
					For intCol = objSheet.Cells(1, 256).End(xlToLeft).Column To 1 Step -1
						strCol = objSheet.Cells(1, intCol).Value						
						If strCol = "Target User" Then
							strSortKey = Replace(objSheet.Cells(1, intCol).Address, "$", "")
						End If
					Next
					strLastCol = Chr(objSheet.Cells(1, 256).End(xlToLeft).Column + 64)
					intLastRow = objSheet.Cells(65536, 1).End(xlUp).Column
				    objSheet.Sort.SortFields.Clear
				    objSheet.Sort.SortFields.Add objSheet.Range(strSortKey), xlSortOnValues, xlAscending, xlSortNormal
				    With objSheet.Sort
				        .SetRange objSheet.Range("A2:" & strLastCol & intLastRow)
				        .Header = xlNo
				        .MatchCase = False
				        .Orientation = xlTopToBottom
				        .SortMethod = xlPinYin
				        .Apply
				    End With
					objExcel.DisplayAlerts = False
					objWB.Save
					objExcel.DisplayAlerts = True
					objWB.Saved = True
					objWB.Close False
					objExcel.Quit
				End If
			Next
		End If
	Next
	WScript.Echo "Finished"
End If

Function ExtractZip(strZipFile, strTargetDir)
	' TITLE: ExtractZip
	' DESCRIPTION: This function will use the embedded decompression ability of
	'	Windows XP or greater, and extract the files to the specified target directory
	' INPUT:
	'	strZipFile specifies the full path to the zip file to decompress
	'	strTargetDir specifies the target folder to extract the files to
	' OUTPUT:
	'	A pipe separated list of file paths will be returned containing the destination
	'	path of each extracted file
	'
	' Create the required Shell objects
	If Right(strTargetDir, 1) <> "\" Then strTargetDir = strTargetDir & "\"
	Set objShellApp = CreateObject("Shell.Application")
	Set objShell = CreateObject("WScript.Shell")
	Set objFSO = CreateObject("Scripting.FileSystemObject")
	' Set option values to prevent prompting and any GUI display
	intOptions = 1024 + 512 + 16 + 4
	'On Error Resume Next
	' Create a reference to the files and folders in the ZIP file
	Set objSource = objShellApp.NameSpace(strZipFile)

	strZipFileList = ""
	strZipFileList = EnumZipFiles(strZipFile, strZipFileList)
	strZipFileList = Replace(strZipFileList, strZipFile & "\", strTargetDir)

	' Create a reference to the target folder
	Set objTarget = objShellApp.NameSpace(strTargetDir)
	' UnZIP the files
	'objTarget.CopyHere objSource.Items(), intOptions
	If Err.Number <> 0 Then WScript.Echo "Unable to unzip " & strZipFile & ". Error " & Err.Number & ": " & Err.Description
	Err.Clear
	On Error Goto 0
	' Release the objects
	Set objSource = Nothing
	Set objTarget = Nothing
		
	ExtractZip = strZipFileList
End Function

Function EnumZipFiles(strZipFolderPath, ByRef strZipFileList)
	If Right(strZipFolderPath, 1) = "\" Then strZipFolderPath = Left(strZipFolderPath, Len(strZipFolderPath) - 1)
	Set objShellApp = CreateObject("Shell.Application")
	Set objZipSource = objShellApp.NameSpace(strZipFolderPath)
	For Each objItem In objZipSource.Items()
		If strZipFileList = "" Then
			strZipFileList = strZipFolderPath & "\" & objItem
		Else
			strZipFileList = strZipFileList & "|" & strZipFolderPath & "\" & objItem
		End If
		If objZipSource.GetDetailsOf(objItem, 1) = "File folder" Then
			strZipFileList = EnumZipFiles(strZipFolderPath & "\" & objItem, strZipFileList)
		End If
	Next
	EnumZipFiles = strZipFileList
End Function

Open in new window

0
 
RobSampsonCommented:
Oops, left the actual extraction commented out...
Set objShell = CreateObject("WScript.Shell")
strTargetAttachmentFolder = objShell.SpecialFolders("Desktop") & "\Test"

If Right(strTargetAttachmentFolder, 1) <> "\" Then strTargetAttachmentFolder = strTargetAttachmentFolder & "\"

strDetachedFiles = objShell.SpecialFolders("Desktop") & "\Test.zip"

Const xlToLeft = -4159
Const xlSortOnValues = 0
Const xlAscending = 1
Const xlSortNormal = 0
Const xlUp = -4162
Const xlNo = 2
Const xlYes = 1
Const xlTopToBottom = 1
Const xlPinYin = 1

If strDetachedFiles = "" Then
	WScript.Echo "No files were detached."
Else
	For Each strFilePath In Split(strDetachedFiles, "|")
		If Right(LCase(strFilePath), 4) = ".zip" Then
			strExtractedFiles = ExtractZip(strFilePath, strTargetAttachmentFolder)
			For Each strExtractedFile In Split(strExtractedFiles, "|")
				If Right(LCase(strExtractedFile), 4) = ".csv" Then
					Set objExcel = CreateObject("Excel.Application")
					objExcel.Visible = True
					Set objWB = objExcel.Workbooks.Open(strExtractedFile, False, False)
					Set objSheet = objWB.Sheets(1)
					For intCol = objSheet.Cells(1, 256).End(xlToLeft).Column To 1 Step -1
						strCol = objSheet.Cells(1, intCol).Value
						If strCol <> "Event TimeStamp (UTC)" And strCol <> "Network Node Address" And strCol <> "Target User" And strCol <> "Source Computer" Then
							objSheet.Columns(Chr(intCol + 64) & ":" & Chr(intCol + 64)).Delete xlToLeft
						End If
					Next
					For intCol = objSheet.Cells(1, 256).End(xlToLeft).Column To 1 Step -1
						strCol = objSheet.Cells(1, intCol).Value						
						If strCol = "Target User" Then
							strSortKey = Replace(objSheet.Cells(1, intCol).Address, "$", "")
						End If
					Next
					strLastCol = Chr(objSheet.Cells(1, 256).End(xlToLeft).Column + 64)
					intLastRow = objSheet.Cells(65536, 1).End(xlUp).Column
				    objSheet.Sort.SortFields.Clear
				    objSheet.Sort.SortFields.Add objSheet.Range(strSortKey), xlSortOnValues, xlAscending, xlSortNormal
				    With objSheet.Sort
				        .SetRange objSheet.Range("A2:" & strLastCol & intLastRow)
				        .Header = xlNo
				        .MatchCase = False
				        .Orientation = xlTopToBottom
				        .SortMethod = xlPinYin
				        .Apply
				    End With
					objExcel.DisplayAlerts = False
					objWB.Save
					objExcel.DisplayAlerts = True
					objWB.Saved = True
					objWB.Close False
					objExcel.Quit
				End If
			Next
		End If
	Next
	WScript.Echo "Finished"
End If

Function ExtractZip(strZipFile, strTargetDir)
	' TITLE: ExtractZip
	' DESCRIPTION: This function will use the embedded decompression ability of
	'	Windows XP or greater, and extract the files to the specified target directory
	' INPUT:
	'	strZipFile specifies the full path to the zip file to decompress
	'	strTargetDir specifies the target folder to extract the files to
	' OUTPUT:
	'	A pipe separated list of file paths will be returned containing the destination
	'	path of each extracted file
	'
	' Create the required Shell objects
	If Right(strTargetDir, 1) <> "\" Then strTargetDir = strTargetDir & "\"
	Set objShellApp = CreateObject("Shell.Application")
	Set objShell = CreateObject("WScript.Shell")
	Set objFSO = CreateObject("Scripting.FileSystemObject")
	' Set option values to prevent prompting and any GUI display
	intOptions = 1024 + 512 + 16 + 4
	'On Error Resume Next
	' Create a reference to the files and folders in the ZIP file
	Set objSource = objShellApp.NameSpace(strZipFile)

	strZipFileList = ""
	strZipFileList = EnumZipFiles(strZipFile, strZipFileList)
	strZipFileList = Replace(strZipFileList, strZipFile & "\", strTargetDir)

	' Create a reference to the target folder
	Set objTarget = objShellApp.NameSpace(strTargetDir)
	' UnZIP the files
	objTarget.CopyHere objSource.Items(), intOptions
	If Err.Number <> 0 Then WScript.Echo "Unable to unzip " & strZipFile & ". Error " & Err.Number & ": " & Err.Description
	Err.Clear
	On Error Goto 0
	' Release the objects
	Set objSource = Nothing
	Set objTarget = Nothing
		
	ExtractZip = strZipFileList
End Function

Function EnumZipFiles(strZipFolderPath, ByRef strZipFileList)
	If Right(strZipFolderPath, 1) = "\" Then strZipFolderPath = Left(strZipFolderPath, Len(strZipFolderPath) - 1)
	Set objShellApp = CreateObject("Shell.Application")
	Set objZipSource = objShellApp.NameSpace(strZipFolderPath)
	For Each objItem In objZipSource.Items()
		If strZipFileList = "" Then
			strZipFileList = strZipFolderPath & "\" & objItem
		Else
			strZipFileList = strZipFileList & "|" & strZipFolderPath & "\" & objItem
		End If
		If objZipSource.GetDetailsOf(objItem, 1) = "File folder" Then
			strZipFileList = EnumZipFiles(strZipFolderPath & "\" & objItem, strZipFileList)
		End If
	Next
	EnumZipFiles = strZipFileList
End Function

Open in new window

0
 
itsmevicAuthor Commented:
Very nice Rob I will surely test this when I get to the office in the A.M.  As always, a ton of thank you's for your expertise and your assistance.   I'll let you know how this latest round goes manana...
0
 
itsmevicAuthor Commented:
Hey there Rob, ran the latest and the greatest this morning in regard to the script above and it's giving me this error:

Script:  
Line:  164
Char:  Expected end of statement
Code:  800A0401
Source:  Microsoft VBScript compliation error

As mentioned, I am running the script from the c:\windows\syswow64 directory from a command prompt.
0
 
RobSampsonCommented:
Line 164??  There's only 122 lines of code there....
0
 
RobSampsonCommented:
Wait, there's 162 lines....the second block I posted didn't have the Lotus Notes stuff...

Don't forget to use the Select All link under the code snippet text box, so you can select all the code, then copy and paste.

Rob.
Set objShell = CreateObject("WScript.Shell")

strPassWd = InputBox("Enter your Lotus Notes password:", "Lots Notes Password")
strFolderName = "($Inbox)"
strSubjectToFind = "Consistent Subject of Message"
strTargetAttachmentFolder = objShell.SpecialFolders("Desktop")

Const xlToLeft = -4159
Const xlSortOnValues = 0
Const xlAscending = 1
Const xlSortNormal = 0
Const xlUp = -4162
Const xlNo = 2
Const xlYes = 1
Const xlTopToBottom = 1
Const xlPinYin = 1

If Right(strTargetAttachmentFolder, 1) <> "\" Then strTargetAttachmentFolder = strTargetAttachmentFolder & "\"
' Initialize Notes
Set objSession = CreateObject("Lotus.NotesSession")
Call objSession.Initialize(strPassWd)
' Open current user's mail file.
strServer = objSession.GetEnvironmentString("MailServer", True)
strFile = objSession.GetEnvironmentString("MailFile", True)
Set objDB = objSession.GetDatabase(strServer, strFile, False)
' Open and read the specified folder.
On Error Resume Next
Set objFolder = objDB.GetView(strFolderName)
If (objFolder Is Nothing) Then
	MsgBox "Error attaching to folder " & strFolderName
ElseIf (objFolder.AllEntries.Count = 0) Then
	MsgBox strFolderName & " contains no messages."
Else
	Err.Clear
	On Error GoTo 0
	strDetachedFiles = ""
	Set objDoc = objFolder.GetFirstDocument
	WScript.Echo "Messages in " & strFolderName
	Do Until (objDoc Is Nothing)
		strSubject = objDoc.GetItemValue("Subject")(0)
		If InStr(strSubject, strSubjectToFind) > 0 Then
			strBody = Replace(objDoc.GetFirstItem("Body").Text, vbCrLf, vbLf)
			WScript.Echo strSubject
			For Each objFile In objDoc.GetFirstItem("Body").EmbeddedObjects
				WScript.Echo objFile.Name
				Call objFile.ExtractFile(strTargetAttachmentFolder & objFile.Name)
				If strDetachedFiles = "" Then
					strDetachedFiles = strTargetAttachmentFolder & objFile.Name
				Else
					strDetachedFiles = strDetachedFiles & "|" & strTargetAttachmentFolder & objFile.Name
				End If
			Next
		End If
		Set objDoc = objFolder.GetNextDocument(objDoc)
	Loop
End If

If strDetachedFiles = "" Then
	WScript.Echo "No files were detached."
Else
	For Each strFilePath In Split(strDetachedFiles, "|")
		If Right(LCase(strFilePath), 4) = ".zip" Then
			strExtractedFiles = ExtractZip(strFilePath, strTargetAttachmentFolder)
			For Each strExtractedFile In Split(strExtractedFiles, "|")
				If Right(LCase(strExtractedFile), 4) = ".csv" Then
					Set objExcel = CreateObject("Excel.Application")
					objExcel.Visible = True
					Set objWB = objExcel.Workbooks.Open(strExtractedFile, False, False)
					Set objSheet = objWB.Sheets(1)
					For intCol = objSheet.Cells(1, 256).End(xlToLeft).Column To 1 Step -1
						strCol = objSheet.Cells(1, intCol).Value
						If strCol <> "Event TimeStamp (UTC)" And strCol <> "Network Node Address" And strCol <> "Target User" And strCol <> "Source Computer" Then
							objSheet.Columns(Chr(intCol + 64) & ":" & Chr(intCol + 64)).Delete xlToLeft
						End If
					Next
					For intCol = objSheet.Cells(1, 256).End(xlToLeft).Column To 1 Step -1
						strCol = objSheet.Cells(1, intCol).Value						
						If strCol = "Target User" Then
							strSortKey = Replace(objSheet.Cells(1, intCol).Address, "$", "")
						End If
					Next
					strLastCol = Chr(objSheet.Cells(1, 256).End(xlToLeft).Column + 64)
					intLastRow = objSheet.Cells(65536, 1).End(xlUp).Column
				    objSheet.Sort.SortFields.Clear
				    objSheet.Sort.SortFields.Add objSheet.Range(strSortKey), xlSortOnValues, xlAscending, xlSortNormal
				    With objSheet.Sort
				        .SetRange objSheet.Range("A2:" & strLastCol & intLastRow)
				        .Header = xlNo
				        .MatchCase = False
				        .Orientation = xlTopToBottom
				        .SortMethod = xlPinYin
				        .Apply
				    End With
					objExcel.DisplayAlerts = False
					objWB.Save
					objExcel.DisplayAlerts = True
					objWB.Saved = True
					objWB.Close False
					objExcel.Quit
				End If
			Next
		End If
	Next
	WScript.Echo "Finished"
End If

Function ExtractZip(strZipFile, strTargetDir)
	' TITLE: ExtractZip
	' DESCRIPTION: This function will use the embedded decompression ability of
	'	Windows XP or greater, and extract the files to the specified target directory
	' INPUT:
	'	strZipFile specifies the full path to the zip file to decompress
	'	strTargetDir specifies the target folder to extract the files to
	' OUTPUT:
	'	A pipe separated list of file paths will be returned containing the destination
	'	path of each extracted file
	'
	' Create the required Shell objects
	If Right(strTargetDir, 1) <> "\" Then strTargetDir = strTargetDir & "\"
	Set objShellApp = CreateObject("Shell.Application")
	Set objShell = CreateObject("WScript.Shell")
	Set objFSO = CreateObject("Scripting.FileSystemObject")
	' Set option values to prevent prompting and any GUI display
	intOptions = 1024 + 512 + 16 + 4
	'On Error Resume Next
	' Create a reference to the files and folders in the ZIP file
	Set objSource = objShellApp.NameSpace(strZipFile)

	strZipFileList = ""
	strZipFileList = EnumZipFiles(strZipFile, strZipFileList)
	strZipFileList = Replace(strZipFileList, strZipFile & "\", strTargetDir)

	' Create a reference to the target folder
	Set objTarget = objShellApp.NameSpace(strTargetDir)
	' UnZIP the files
	objTarget.CopyHere objSource.Items(), intOptions
	If Err.Number <> 0 Then WScript.Echo "Unable to unzip " & strZipFile & ". Error " & Err.Number & ": " & Err.Description
	Err.Clear
	On Error Goto 0
	' Release the objects
	Set objSource = Nothing
	Set objTarget = Nothing
		
	ExtractZip = strZipFileList
End Function

Function EnumZipFiles(strZipFolderPath, ByRef strZipFileList)
	If Right(strZipFolderPath, 1) = "\" Then strZipFolderPath = Left(strZipFolderPath, Len(strZipFolderPath) - 1)
	Set objShellApp = CreateObject("Shell.Application")
	Set objZipSource = objShellApp.NameSpace(strZipFolderPath)
	For Each objItem In objZipSource.Items()
		If strZipFileList = "" Then
			strZipFileList = strZipFolderPath & "\" & objItem
		Else
			strZipFileList = strZipFileList & "|" & strZipFolderPath & "\" & objItem
		End If
		If objZipSource.GetDetailsOf(objItem, 1) = "File folder" Then
			strZipFileList = EnumZipFiles(strZipFolderPath & "\" & objItem, strZipFileList)
		End If
	Next
	EnumZipFiles = strZipFileList
End Function

Open in new window

0
 
itsmevicAuthor Commented:
You know, I never even noticed that function until you mentioned it, that is the "Select All"....lol, you'd think I would of caught that a long time ago.  I will surely give this ago in the A.M.  and I'll let you know how it goes.  Thanks again for your help!  
0
 
itsmevicAuthor Commented:
Hi Rob, was able to test the latest code above and it works like a charm.  The only thing I noticed, was instead of creating four columns as it should it's only creating one column withl all of the data combined in the A column of the spreadsheet.  

 Sample
0
 
RobSampsonCommented:
OK, that must be down to the format of your original CSV.  We might have to tell Excel to open it differently. Could you possibly upload a sample CSV, and I'll get it to work with that.

Rob.
0
 
itsmevicAuthor Commented:
Hi Rob,

    I'll provide you with a sample .CSV of the exact format with moch data of what normally would be there.  Will upload this soon as I get back into the office.  Thanks again!  
0
 
itsmevicAuthor Commented:
Hey there Rob,

     Attached is the sample format you requested.  This is exactly how the headers are listed in the .CSV report.  I've included basically just two rows w/bogus data underneath those headers.  I think that should give you some indication on how it's all formatted.   Thank you.  




Sample-CSV-Format.xls
0
 
RobSampsonCommented:
Hi,

The file you have attached is XLS format, not CSV format.  CSV files are pretty much plain text files.  If your file in the email is actually XLS, we'll open it differently.  Otherwise, please upload the CSV formatted file that you get from the email (with data changed).

Regards,

Rob.
0
 
RobSampsonCommented:
P.S. I *could* do "save as" CSV, but I'm not guaranteed to get exactly the same format you would receive.
0
 
itsmevicAuthor Commented:
oops I apologize man.  I'll re-load first thing in A.M. (as a .CSV).  Thanks for bringing that to my attention.  I'm so used to saving regular spreadsheets and had a total brain fart, overlooking the fact that it might of helped if I kept the original format.  : ) -
0
 
itsmevicAuthor Commented:
Here's the .CSV Rob (minus the original data).  You were right.  After I remove the original data and do a "Save As" instead of the multiple columns as show here when open

 Sample Sample
After the "Save As" I get all of these columns jumbled up in ONE column the A column as shown below:

Image 2
 Sample
I'm able to import this into a new Excel document by doing a Data>From Text  Selecting my Sample.CSV file in the above (image 2) then selecting "Import", then Choosing "Delimited" NEXT and then setting my Delimiters as "TAB"

I'm open to whatever you think would work best in this case...The headers you see in each column are exact as shown in the extracted ZIP.  
Sample.csv
0
 
RobSampsonCommented:
Hi, well, in an effort to leave it at as little work for you as possible, I've added a MassageTextFile procedure, that will open the oddly formatted file (which technically is a tab separated file, but anyway...), and replace the tab characters with "," so that it becomes a proper CSV.  Then, Excel opens it normally in order to remove the columns.

Regards,

Rob.
Set objShell = CreateObject("WScript.Shell")

strPassWd = InputBox("Enter your Lotus Notes password:", "Lots Notes Password")
strFolderName = "($Inbox)"
strSubjectToFind = "Consistent Subject of Message"
strTargetAttachmentFolder = objShell.SpecialFolders("Desktop")

Const xlToLeft = -4159
Const xlSortOnValues = 0
Const xlAscending = 1
Const xlSortNormal = 0
Const xlUp = -4162
Const xlNo = 2
Const xlYes = 1
Const xlTopToBottom = 1
Const xlPinYin = 1

If Right(strTargetAttachmentFolder, 1) <> "\" Then strTargetAttachmentFolder = strTargetAttachmentFolder & "\"
' Initialize Notes
Set objSession = CreateObject("Lotus.NotesSession")
Call objSession.Initialize(strPassWd)
' Open current user's mail file.
strServer = objSession.GetEnvironmentString("MailServer", True)
strFile = objSession.GetEnvironmentString("MailFile", True)
Set objDB = objSession.GetDatabase(strServer, strFile, False)
' Open and read the specified folder.
On Error Resume Next
Set objFolder = objDB.GetView(strFolderName)
If (objFolder Is Nothing) Then
	WScript.Echo "Error attaching to folder " & strFolderName
ElseIf (objFolder.AllEntries.Count = 0) Then
	WScript.Echo strFolderName & " contains no messages."
Else
	Err.Clear
	On Error GoTo 0
	strDetachedFiles = ""
	Set objDoc = objFolder.GetFirstDocument
	Do Until (objDoc Is Nothing)
		strSubject = objDoc.GetItemValue("Subject")(0)
		If InStr(strSubject, strSubjectToFind) > 0 Then
			strBody = Replace(objDoc.GetFirstItem("Body").Text, vbCrLf, vbLf)
			For Each objFile In objDoc.GetFirstItem("Body").EmbeddedObjects
				Call objFile.ExtractFile(strTargetAttachmentFolder & objFile.Name)
				If strDetachedFiles = "" Then
					strDetachedFiles = strTargetAttachmentFolder & objFile.Name
				Else
					strDetachedFiles = strDetachedFiles & "|" & strTargetAttachmentFolder & objFile.Name
				End If
			Next
		End If
		Set objDoc = objFolder.GetNextDocument(objDoc)
	Loop
End If

If strDetachedFiles = "" Then
	WScript.Echo "No files were detached."
Else
	For Each strFilePath In Split(strDetachedFiles, "|")
		If Right(LCase(strFilePath), 4) = ".zip" Then
			strExtractedFiles = ExtractZip(strFilePath, strTargetAttachmentFolder)
			For Each strExtractedFile In Split(strExtractedFiles, "|")
				If Right(LCase(strExtractedFile), 4) = ".csv" Then
					MassageTextFile strExtractedFile
					Set objExcel = CreateObject("Excel.Application")
					objExcel.Visible = True
					Set objWB = objExcel.Workbooks.Open(strExtractedFile, False, False)
					Set objSheet = objWB.Sheets(1)
					For intCol = objSheet.Cells(1, 256).End(xlToLeft).Column To 1 Step -1
						strCol = objSheet.Cells(1, intCol).Value
						If strCol <> "Event Timestamp (UTC)" And strCol <> "Network Node Address" And strCol <> "Target User" And strCol <> "Source Computer" Then
							objSheet.Columns(Chr(intCol + 64) & ":" & Chr(intCol + 64)).Delete xlToLeft
						End If
					Next
					For intCol = objSheet.Cells(1, 256).End(xlToLeft).Column To 1 Step -1
						strCol = objSheet.Cells(1, intCol).Value						
						If strCol = "Target User" Then
							strSortKey = Replace(objSheet.Cells(1, intCol).Address, "$", "")
						End If
					Next
					strLastCol = Chr(objSheet.Cells(1, 256).End(xlToLeft).Column + 64)
					intLastRow = objSheet.Cells(65536, 1).End(xlUp).Column
				    objSheet.Sort.SortFields.Clear
				    objSheet.Sort.SortFields.Add objSheet.Range(strSortKey), xlSortOnValues, xlAscending, xlSortNormal
				    With objSheet.Sort
				        .SetRange objSheet.Range("A2:" & strLastCol & intLastRow)
				        .Header = xlNo
				        .MatchCase = False
				        .Orientation = xlTopToBottom
				        .SortMethod = xlPinYin
				        .Apply
				    End With
					objExcel.DisplayAlerts = False
					objWB.Save
					objExcel.DisplayAlerts = True
					objWB.Saved = True
					objWB.Close False
					objExcel.Quit
				End If
			Next
		End If
	Next
	WScript.Echo "Finished"
End If

Function ExtractZip(strZipFile, strTargetDir)
	' TITLE: ExtractZip
	' DESCRIPTION: This function will use the embedded decompression ability of
	'	Windows XP or greater, and extract the files to the specified target directory
	' INPUT:
	'	strZipFile specifies the full path to the zip file to decompress
	'	strTargetDir specifies the target folder to extract the files To
	' OUTPUT:
	'	A pipe separated list of file paths will be returned containing the destination
	'	path of each extracted file
	'
	' Create the required Shell objects
	If Right(strTargetDir, 1) <> "\" Then strTargetDir = strTargetDir & "\"
	Set objShellApp = CreateObject("Shell.Application")
	Set objShell = CreateObject("WScript.Shell")
	Set objFSO = CreateObject("Scripting.FileSystemObject")
	' Set option values to prevent prompting and any GUI display
	intOptions = 1024 + 512 + 16 + 4
	'On Error Resume Next
	' Create a reference to the files and folders in the ZIP file
	Set objSource = objShellApp.NameSpace(strZipFile)

	strZipFileList = ""
	strZipFileList = EnumZipFiles(strZipFile, strZipFileList)
	strZipFileList = Replace(strZipFileList, strZipFile & "\", strTargetDir)

	' Create a reference to the target folder
	Set objTarget = objShellApp.NameSpace(strTargetDir)
	' UnZIP the files
	objTarget.CopyHere objSource.Items(), intOptions
	If Err.Number <> 0 Then WScript.Echo "Unable to unzip " & strZipFile & ". Error " & Err.Number & ": " & Err.Description
	Err.Clear
	On Error Goto 0
	' Release the objects
	Set objSource = Nothing
	Set objTarget = Nothing
		
	ExtractZip = strZipFileList
End Function

Function EnumZipFiles(strZipFolderPath, ByRef strZipFileList)
	If Right(strZipFolderPath, 1) = "\" Then strZipFolderPath = Left(strZipFolderPath, Len(strZipFolderPath) - 1)
	Set objShellApp = CreateObject("Shell.Application")
	Set objZipSource = objShellApp.NameSpace(strZipFolderPath)
	For Each objItem In objZipSource.Items()
		If strZipFileList = "" Then
			strZipFileList = strZipFolderPath & "\" & objItem
		Else
			strZipFileList = strZipFileList & "|" & strZipFolderPath & "\" & objItem
		End If
		If objZipSource.GetDetailsOf(objItem, 1) = "File folder" Then
			strZipFileList = EnumZipFiles(strZipFolderPath & "\" & objItem, strZipFileList)
		End If
	Next
	EnumZipFiles = strZipFileList
End Function

Sub MassageTextFile(strFilePath)
	Set objFSO = CreateObject("Scripting.FileSystemObject")
	Const ForReading = 1
	strTempFile = strFilePath & "_2"
	Set objFile = objFSO.OpenTextFile(strFilePath, ForReading, False)
	Set objTemp = objFSO.CreateTextFile(strTempFile, True)
	While Not objFile.AtEndOfStream
		strLine = Trim(objFile.ReadLine)
		If strLine <> "" Then
			If Replace(strLine, vbTab, "") <> """""" Then
				objTemp.WriteLine Replace(strLine, vbTab, """,""")
			End If
		End If
	Wend
	objFile.Close
	objTemp.Close
	objFSO.DeleteFile strFilePath, True
	objFSO.MoveFile strTempFile, strFilePath
End Sub

Open in new window

0
 
itsmevicAuthor Commented:
Awesome Rob! Will give it a go in the A.M. and let yo know!  Thank you again!
0
 
itsmevicAuthor Commented:
Hey there Rob! Had an opportunity to test the latest and the greatest above.   It's a beautiful thing! LOL and works "awesomely"  and your right it does now open  as a "True" .CSV and the data appears to be "cleaner" (if that description makes sense).  However, even though the data appears cleaner and is in true .CSV format now all of the data is one column.  

Is there anyway we could tell it to include the column headers indicated and place the appropriate data under those columns?   I know the data in the original extracted .CSV file is a little squirrlly, I totally understand so my fingers are crossed that this can be done.  As you know, the columns I'm talking about are:


Event TimeStamp (UTC)
Network Node Address
Target User
Source Computer

I think if it could do this it would be so much easier to read report wise rather than looking at this CSV and trying to pick out things in the A column only.  I hope that makes sense.  

Proposed Format
header       header        header           header
data              data             data               data
data              data             data               data
data              data             data               data
""                   ""                 ""                    ""
""                   ""                 ""                    ""

as opposed to:

                         Column A
mm/dd/yyyy 111.111.111.111USER1Computer1
mm//dd/yyyy 222.222.222.222USER2Computer2
''""
""
""
and so on...

As always your greatly appreciated!  Thanks for your help with this Rob!
0
 
RobSampsonCommented:
Hi, that seems very strange.  When I take your sample.csv that you uploaded, see in notepad that each line starts and ends with double quotes, and there's a bunch of tab characters in the middle as field separators.

After I run the MassageTextFile procedure on the detached and extracted CSV, here's how it looks:
"Analyzer Node Ident","Event Timestamp (UTC)","NetIQ Event Classification","Event Id","Event Source","Network Node Name","Network Node Address","Source Computer","Source Address","Target Computer","Target Address","Source User","Target User","Target Process","Target Object Name","Target Object Type","Target Service","Message","Logon Type","Logon Process","Object Properties","New Account Name","Session Name","Correlation ID"
"{00000003-0000-0000-0000-000000000000}","10/17/2011 11:11","","640","Security","0000-000","111.11.111.111","COMPUTER1","000.00.000.000","","","TEST\USER1","TEST\USER2","","","","","","","","","","",""
"{00000000-0000-0000-0000-000000000000}","10/17/2011 11:31","","4141","Microsoft-Windows-Security-Auditing","0000-000","222.22.222.222","COMPUTER2","000.00.000.000","","","TEST\USER3","TEST\USER19","","","","","","","","","","",""
"{0000000-00000-00000-0000-00000000000}","10/17/2011 11:31","","640","Security","COMPUTER10","333.33.333.333","COMPUTER20","000.00.000.000","","","TEST\USER4","TEST\USER5","","","","","","","","","","",""
"{000000-00000-00000-00000-00000000000}","10/17/2011 10:41","","640","Security","COMPUTER15","444.44.444.444","COMPUTER30","000.00.000.000","","","TEST\USER8","TEST\USER9","","","","","","","","","","",""

Open in new window


Then, after it uses Excel to change the columns, here's how it looks in Notepad:
Event Timestamp (UTC),Network Node Address,Source Computer,Target User
10/17/2011 11:11,111.11.111.111,COMPUTER1,TEST\USER2
10/17/2011 11:31,222.22.222.222,COMPUTER2,TEST\USER19
10/17/2011 11:31,333.33.333.333,COMPUTER20,TEST\USER5
10/17/2011 10:41,444.44.444.444,COMPUTER30,TEST\USER9

Open in new window


so you can see, it has the headers, and each field separated by a comma, so Excel should open it just fine.

One thing I haven't asked you is what version of Excel you're using?

Rob.
0
 
RobSampsonCommented:
Oh, and here I've separated the Excel massaging to it's own sub, so you can comment out
					MassageCSVFileWithExcel strExtractedFile

Open in new window


to just see the results of the first massaging.

Regards,

Rob.
Set objShell = CreateObject("WScript.Shell")

strPassWd = InputBox("Enter your Lotus Notes password:", "Lots Notes Password")
strFolderName = "($Inbox)"
strSubjectToFind = "Consistent Subject of Message"
strTargetAttachmentFolder = objShell.SpecialFolders("Desktop")

Const xlToLeft = -4159
Const xlSortOnValues = 0
Const xlAscending = 1
Const xlSortNormal = 0
Const xlUp = -4162
Const xlNo = 2
Const xlYes = 1
Const xlTopToBottom = 1
Const xlPinYin = 1

If Right(strTargetAttachmentFolder, 1) <> "\" Then strTargetAttachmentFolder = strTargetAttachmentFolder & "\"
' Initialize Notes
Set objSession = CreateObject("Lotus.NotesSession")
Call objSession.Initialize(strPassWd)
' Open current user's mail file.
strServer = objSession.GetEnvironmentString("MailServer", True)
strFile = objSession.GetEnvironmentString("MailFile", True)
Set objDB = objSession.GetDatabase(strServer, strFile, False)
' Open and read the specified folder.
On Error Resume Next
Set objFolder = objDB.GetView(strFolderName)
If (objFolder Is Nothing) Then
	WScript.Echo "Error attaching to folder " & strFolderName
ElseIf (objFolder.AllEntries.Count = 0) Then
	WScript.Echo strFolderName & " contains no messages."
Else
	Err.Clear
	On Error GoTo 0
	strDetachedFiles = ""
	Set objDoc = objFolder.GetFirstDocument
	Do Until (objDoc Is Nothing)
		strSubject = objDoc.GetItemValue("Subject")(0)
		If InStr(strSubject, strSubjectToFind) > 0 Then
			strBody = Replace(objDoc.GetFirstItem("Body").Text, vbCrLf, vbLf)
			For Each objFile In objDoc.GetFirstItem("Body").EmbeddedObjects
				Call objFile.ExtractFile(strTargetAttachmentFolder & objFile.Name)
				If strDetachedFiles = "" Then
					strDetachedFiles = strTargetAttachmentFolder & objFile.Name
				Else
					strDetachedFiles = strDetachedFiles & "|" & strTargetAttachmentFolder & objFile.Name
				End If
			Next
		End If
		Set objDoc = objFolder.GetNextDocument(objDoc)
	Loop
End If

If strDetachedFiles = "" Then
	WScript.Echo "No files were detached."
Else
	For Each strFilePath In Split(strDetachedFiles, "|")
		If Right(LCase(strFilePath), 4) = ".zip" Then
			strExtractedFiles = ExtractZip(strFilePath, strTargetAttachmentFolder)
			For Each strExtractedFile In Split(strExtractedFiles, "|")
				If Right(LCase(strExtractedFile), 4) = ".csv" Then
					MassageTextFile strExtractedFile
					MassageCSVFileWithExcel strExtractedFile
				End If
			Next
		End If
	Next
	WScript.Echo "Finished"
End If

Function ExtractZip(strZipFile, strTargetDir)
	' TITLE: ExtractZip
	' DESCRIPTION: This function will use the embedded decompression ability of
	'	Windows XP or greater, and extract the files to the specified target directory
	' INPUT:
	'	strZipFile specifies the full path to the zip file to decompress
	'	strTargetDir specifies the target folder to extract the files To
	' OUTPUT:
	'	A pipe separated list of file paths will be returned containing the destination
	'	path of each extracted file
	'
	' Create the required Shell objects
	If Right(strTargetDir, 1) <> "\" Then strTargetDir = strTargetDir & "\"
	Set objShellApp = CreateObject("Shell.Application")
	Set objShell = CreateObject("WScript.Shell")
	Set objFSO = CreateObject("Scripting.FileSystemObject")
	' Set option values to prevent prompting and any GUI display
	intOptions = 1024 + 512 + 16 + 4
	'On Error Resume Next
	' Create a reference to the files and folders in the ZIP file
	Set objSource = objShellApp.NameSpace(strZipFile)

	strZipFileList = ""
	strZipFileList = EnumZipFiles(strZipFile, strZipFileList)
	strZipFileList = Replace(strZipFileList, strZipFile & "\", strTargetDir)

	' Create a reference to the target folder
	Set objTarget = objShellApp.NameSpace(strTargetDir)
	' UnZIP the files
	objTarget.CopyHere objSource.Items(), intOptions
	If Err.Number <> 0 Then WScript.Echo "Unable to unzip " & strZipFile & ". Error " & Err.Number & ": " & Err.Description
	Err.Clear
	On Error Goto 0
	' Release the objects
	Set objSource = Nothing
	Set objTarget = Nothing
		
	ExtractZip = strZipFileList
End Function

Function EnumZipFiles(strZipFolderPath, ByRef strZipFileList)
	If Right(strZipFolderPath, 1) = "\" Then strZipFolderPath = Left(strZipFolderPath, Len(strZipFolderPath) - 1)
	Set objShellApp = CreateObject("Shell.Application")
	Set objZipSource = objShellApp.NameSpace(strZipFolderPath)
	For Each objItem In objZipSource.Items()
		If strZipFileList = "" Then
			strZipFileList = strZipFolderPath & "\" & objItem
		Else
			strZipFileList = strZipFileList & "|" & strZipFolderPath & "\" & objItem
		End If
		If objZipSource.GetDetailsOf(objItem, 1) = "File folder" Then
			strZipFileList = EnumZipFiles(strZipFolderPath & "\" & objItem, strZipFileList)
		End If
	Next
	EnumZipFiles = strZipFileList
End Function

Sub MassageCSVFileWithExcel(strFilePath)
	Set objExcel = CreateObject("Excel.Application")
	objExcel.Visible = True
	Set objWB = objExcel.Workbooks.Open(strExtractedFile, False, False)
	Set objSheet = objWB.Sheets(1)
	For intCol = objSheet.Cells(1, 256).End(xlToLeft).Column To 1 Step -1
		strCol = objSheet.Cells(1, intCol).Value
		If strCol <> "Event Timestamp (UTC)" And strCol <> "Network Node Address" And strCol <> "Target User" And strCol <> "Source Computer" Then
			objSheet.Columns(Chr(intCol + 64) & ":" & Chr(intCol + 64)).Delete xlToLeft
		End If
	Next
	For intCol = objSheet.Cells(1, 256).End(xlToLeft).Column To 1 Step -1
		strCol = objSheet.Cells(1, intCol).Value						
		If strCol = "Target User" Then
			strSortKey = Replace(objSheet.Cells(1, intCol).Address, "$", "")
		End If
	Next
	strLastCol = Chr(objSheet.Cells(1, 256).End(xlToLeft).Column + 64)
	intLastRow = objSheet.Cells(65536, 1).End(xlUp).Column
    objSheet.Sort.SortFields.Clear
    objSheet.Sort.SortFields.Add objSheet.Range(strSortKey), xlSortOnValues, xlAscending, xlSortNormal
    With objSheet.Sort
        .SetRange objSheet.Range("A2:" & strLastCol & intLastRow)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
	objExcel.DisplayAlerts = False
	objWB.Save
	objExcel.DisplayAlerts = True
	objWB.Saved = True
	objWB.Close False
	objExcel.Quit
End Sub

Sub MassageTextFile(strFilePath)
	Set objFSO = CreateObject("Scripting.FileSystemObject")
	Const ForReading = 1
	strTempFile = strFilePath & "_2"
	Set objFile = objFSO.OpenTextFile(strFilePath, ForReading, False)
	Set objTemp = objFSO.CreateTextFile(strTempFile, True)
	While Not objFile.AtEndOfStream
		strLine = Trim(objFile.ReadLine)
		If strLine <> "" Then
			If Replace(strLine, vbTab, "") <> """""" Then
				objTemp.WriteLine Replace(strLine, vbTab, """,""")
			End If
		End If
	Wend
	objFile.Close
	objTemp.Close
	objFSO.DeleteFile strFilePath, True
	objFSO.MoveFile strTempFile, strFilePath
End Sub

Open in new window

0
 
itsmevicAuthor Commented:
Rob,

    I'm using Office 2007.  Hey Rob, I don't know if this will help in our endeavor here but thought I'd try anyway.  I've attached another .CSV file in EXACT format.  The headers are identical the data is (for the most part identical in format).   Perhaps this will help us.  I think it's a little cleaner than what I've provided you thus far and is probably the reason why the output file isn't turning out how we need to to.  i.e. everything in the A column instead of separated in different columns with header info.  

     This one is turning into a booger I know, that's for your persistence with this! .

 Sample-File.csv
0
 
RobSampsonCommented:
Well I'm using Office 2007 too, and the file you have just uploaded *is* a proper CSV, which has no quotes, and each field separated by a comma, so Excel should just open it normally.

From the latest code, I just commented out this line:
                              MassageTextFile strExtractedFile

and it worked fine.  My final output still came in the format I posted in comment ID 36996756.

One thing, please change this:
      Set objWB = objExcel.Workbooks.Open(strExtractedFile, False, False)

to this:
      Set objWB = objExcel.Workbooks.Open(strFilePath, False, False)

I still can't see how it would come out in one column.  When you detach the file the you just uploaded, and double click it, does Excel open it in proper columns?  If not, I think there must be some odd way that your Excel is handling CSV files.  You may need to repair your Office installation or something.

Regards,

Rob.
0
 
itsmevicAuthor Commented:
I will surely test it Rob, thanks again man!  
0
 
itsmevicAuthor Commented:
A million thanks Rob! Thank you again sir!
0
 
RobSampsonCommented:
No problem.  Just a side note.  I think I noticed in my testing that if you have Lotus Notes already open, the password isn't required at all.

You can try commenting out
strPassWd = InputBox("Enter your Lotus Notes password:", "Lots Notes Password")

to see what happens.  In my session on 8.5.1 I didn't need it.

Rob.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 17
  • 14
Tackle projects and never again get stuck behind a technical roadblock.
Join Now