importing exchange tracking logs to access
I am having trouble importing exchange 5.5 sp3 tracking logs to access/excel. I realize the logs are tab delimited and select the appropriate button.
What happens is the recepient's name (which should be column 13) appears as a new record. This makes it impossible to utilize the logs. This field is separated from the previous one by a line feed (according to M$), but I can't figure out how to get it to ignore this. During the import you can see a preview of the data you are importing and no matter what I've tried I can't get it formatted properly. I have searched M$ site and can't find much help on this, they simply tell you to import the log as tab delimited.
I am using Access 2000 with NT 4 SP 5. Any ideas?
What happens is the recepient's name (which should be column 13) appears as a new record. This makes it impossible to utilize the logs. This field is separated from the previous one by a line feed (according to M$), but I can't figure out how to get it to ignore this. During the import you can see a preview of the data you are importing and no matter what I've tried I can't get it formatted properly. I have searched M$ site and can't find much help on this, they simply tell you to import the log as tab delimited.
I am using Access 2000 with NT 4 SP 5. Any ideas?
try www.promodag.com -- I've been using it awhile and it works great, it seems to be free, though a nag screen has just recently started pop-ing up, but if that's all then, I'm satisfied
ASKER
I have experience with Promodag, but it limits you to 15 days of import. I think it's a good tool, but too expensive at this time for our needs. It would be more useful if I could import 31 days of data, but I understand that they have to make money too... just too costly for now.
Hi posivibe,
Well I know it's tedious doing it b hand...from my xperience...
so what you can is this
+with the file just import it so the name will be in the next line.
+now press ALT+F11
+then goto :: insert>new module from the menu
+then paste this code
'=======STARTCODE=========
Option Explicit
Sub DR()
' Data Reorder
' I= The counter for the source sheet
' rwIndex = The counter for the rows in the sheet
' clIndex = The counter for the colls in the sheet
Dim I As Integer
Dim rwIndex As Integer
Dim clIndex As Integer
Dim rngCount As Integer
rngCount = (ActiveSheet.Cells.Special
For I = 4 To rngCount
clIndex = 13
rwIndex = I
Sheets("Sheet1").Select
Range("A" & I).Select
Selection.Copy
Cells(rwIndex - 1, clIndex).Select
ActiveSheet.Paste
Rows(I).Delete
Next I
End Sub
'=======ENDCODE===========
+it will start at row 4 thats the I
+it will loop through the sheet
+copying the names in collumn 13
+and delete the then empty row
+if you would test it on the first records to that with F8 and step by step
+if all goes well(tested here :O) ) then just press F5
+after it is finished you can close the code window and delete the macro or whatever
HTH:O)Bruintje
Well I know it's tedious doing it b hand...from my xperience...
so what you can is this
+with the file just import it so the name will be in the next line.
+now press ALT+F11
+then goto :: insert>new module from the menu
+then paste this code
'=======STARTCODE=========
Option Explicit
Sub DR()
' Data Reorder
' I= The counter for the source sheet
' rwIndex = The counter for the rows in the sheet
' clIndex = The counter for the colls in the sheet
Dim I As Integer
Dim rwIndex As Integer
Dim clIndex As Integer
Dim rngCount As Integer
rngCount = (ActiveSheet.Cells.Special
For I = 4 To rngCount
clIndex = 13
rwIndex = I
Sheets("Sheet1").Select
Range("A" & I).Select
Selection.Copy
Cells(rwIndex - 1, clIndex).Select
ActiveSheet.Paste
Rows(I).Delete
Next I
End Sub
'=======ENDCODE===========
+it will start at row 4 thats the I
+it will loop through the sheet
+copying the names in collumn 13
+and delete the then empty row
+if you would test it on the first records to that with F8 and step by step
+if all goes well(tested here :O) ) then just press F5
+after it is finished you can close the code window and delete the macro or whatever
HTH:O)Bruintje
ASKER
I'll give this a shot tomorrow.
ASKER
ok... I tried your code and instructions, but I was unsuccessful. First of all when I import the data I get the recipient record on the second line and I get another blank record below that. To be clear I get 3 records for each one I want.
Secondly I cut and pasted your code and then tried to run the macro I get "xlCellTypeLastCell variable undefined error".
Secondly I cut and pasted your code and then tried to run the macro I get "xlCellTypeLastCell variable undefined error".
Hi,
could you send me a sample of such a file?
then i'll can customize it and look at the exact format.
mulbum@worldonline.nl is the address
could you send me a sample of such a file?
then i'll can customize it and look at the exact format.
mulbum@worldonline.nl is the address
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Adjusted points to 100
ASKER
Your code works well, my only problem is my logs are too big for excel. Let me know if you can figure out a solution for Access. Thanks all.