Mcygee
09-17-2009, 08:09 AM
EDIT: NEVERMIND I found another work around. I was deleting the message body by seeing if column B was blank before. It would be if the data was imported as one long string in column A. But all my good data would have a date in column C. So now I'm just going to check to see if column C has a date, if it doesn't I'll delete the entire row. Thus solving my problem of some of the message data being in column B.
I'm using a basic recorded macro to import data from a CSV file...
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Reports\GroupWise_Calendar.csv", Destination _
:=Range("$A$1"))
.Name = "GroupWise_Calendar"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 65001
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
Specifically, this is data that has been exported from a Novell GroupWise Calendar. Where it comes from is not important, but the fact it exports the body of the appointment is. The code I run deletes the information from the message body most of the time, but the problem comes when the user places commas in the body. Doing this causes Excel to delegate the data in the message body by the comma. Here is why...
A normal line of data with no message body looks like this...
------------
"Library Minilab Closed for Spanish CBE","LHS2000_Mini ","9/4/2009","1:00 PM","9/4/2009","2:30 PM","false","",""
-------------
But when the body is exported it includes all the formatting including line spacing. So you get something like this...
-------------
"Courts and Criminal Procedure - Lanman Internet-Delegated","LHS-LAB1_RM205 ","9/3/2009","11:00 AM","9/3/2009","1:00 PM","false","
>>> D Laan 09/03/09 11:02 >>>
D D. Laan
Lnnial High School
College and Career Magnet -G ISD
dan@grdisd.net
It is the policy of the Gad ISD not to discriminate on the basis of race, color, national origin, sex, handicap, or age in its employment practices and/or its vocational programs, services or activities as required by Title VI of the Civil Rights Act 1964, as amended, Title IX of the Educational Amendments of 1972; the Age Discrimination Act of 1975, as amended, and Section 504 of the Rehabilitation Act of 1973, as amended.
------------
Why I import this data, Excel considers every line it's own line in the spreadsheet. Even though these message body lines don't have text qualifiers (Quotes) around them.
Anyway to import the data but ignore data with no text qualifiers? Or have it import the message body without delegating it by commas?
I'm using a basic recorded macro to import data from a CSV file...
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Reports\GroupWise_Calendar.csv", Destination _
:=Range("$A$1"))
.Name = "GroupWise_Calendar"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 65001
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
Specifically, this is data that has been exported from a Novell GroupWise Calendar. Where it comes from is not important, but the fact it exports the body of the appointment is. The code I run deletes the information from the message body most of the time, but the problem comes when the user places commas in the body. Doing this causes Excel to delegate the data in the message body by the comma. Here is why...
A normal line of data with no message body looks like this...
------------
"Library Minilab Closed for Spanish CBE","LHS2000_Mini ","9/4/2009","1:00 PM","9/4/2009","2:30 PM","false","",""
-------------
But when the body is exported it includes all the formatting including line spacing. So you get something like this...
-------------
"Courts and Criminal Procedure - Lanman Internet-Delegated","LHS-LAB1_RM205 ","9/3/2009","11:00 AM","9/3/2009","1:00 PM","false","
>>> D Laan 09/03/09 11:02 >>>
D D. Laan
Lnnial High School
College and Career Magnet -G ISD
dan@grdisd.net
It is the policy of the Gad ISD not to discriminate on the basis of race, color, national origin, sex, handicap, or age in its employment practices and/or its vocational programs, services or activities as required by Title VI of the Civil Rights Act 1964, as amended, Title IX of the Educational Amendments of 1972; the Age Discrimination Act of 1975, as amended, and Section 504 of the Rehabilitation Act of 1973, as amended.
------------
Why I import this data, Excel considers every line it's own line in the spreadsheet. Even though these message body lines don't have text qualifiers (Quotes) around them.
Anyway to import the data but ignore data with no text qualifiers? Or have it import the message body without delegating it by commas?