Exporting Dates to a prn file

[Joe4]

We are writing a macro that will take a large Excel data file and convert it to a prn file so that we can import into our software.

We discovered that when creating a prn file, Excel wraps characters after space 240 to the next line. We were able to work around that by using the code found in Microsoft Knowledge Base Article - 131554 ( http://support.microsoft.com/default.aspx?scid=kb;en-us;131554 ).

However, we have a little "twist". A few of the fields are date fields, and are in a date format. We need them to be in the prn file in "mmddyyyy" format. Unfortunately, no matter which format we put it in, the code from the MS Knowledge Base puts it in numeric format (i.e. 9/17/03 exports as 37881).

Using the code from the MS Knowledge Base, what can I do to get the date to export in my desired format (i.e. 09172003)? I can write code to build a text string from the actual date, but I must be putting in the wrong place because it isn't working. Or is there a easier or better way?

BTW, here is code I wrote:

                If IsDate(Cells(RowNum, ColNum)) Then
                    Dim MyMonth, MyDay, MyYear As String
                    MyMonth = Month(Cells(RowNum, ColNum))
                    If Len(MyMonth) = 1 Then MyMonth = "0" & MyMonth
                    MyDay = Day(Cells(RowNum, ColNum))
                    If Len(MyDay) = 1 Then MyDay = "0" & MyDay
                    MyYear = Year(Cells(RowNum, ColNum))
                    Cells(RowNum, ColNum).NumberFormat = "@"
                    Cells(RowNum, ColNum) = MyMonth & MyDay & MyYear
                End If

 

[TommyGun]

Okay, inside the code that is from the KB article, replace this section...

<font face=Courier New><SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> .HorizontalAlignment
    <SPAN style="color:#00007F">Case</SPAN> xlRight
        CellText = Space(ColWidth - Len(.Text)) & CheckForDate(.Text)
    <SPAN style="color:#00007F">Case</SPAN> xlCenter
        CellText = Space((ColWidth - Len(.Text)) / 2) & CheckForDate(.Text) & _
        Space((ColWidth - Len(.Text)) / 2)
    <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN>
        CellText = CheckForDate(.Text) & Space(ColWidth - Len(.Text))
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN></FONT>

Then insert this formula into your module...

<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> CheckForDate(value) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">If</SPAN> IsDate(value) <SPAN style="color:#00007F">Then</SPAN>
        CheckForDate = <SPAN style="color:#00007F">CStr</SPAN>(Format(value, "mmddyyyy"))
    <SPAN style="color:#00007F">Else</SPAN>
        CheckForDate = <SPAN style="color:#00007F">CStr</SPAN>(value)
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

This is not tested, so there might still be issues.

 

[Joe4]

TommyGun,

Thanks for taking a stab at this.

I tested it out, and unfortunately, it has the same problem that my code did... it is exporting the date in "General" format (i.e. 9/17/03 exporting as 37881). Do you get the same results if you test it yourself?

 

[TommyGun]

I haven't tested it with a full export of data, but the function seems to work fine.with data formatted as dates. Is your data formatted as a number, or are you still running the conversion code that you posted earlier?

 

[Joe4]

I took out what I had and used your additions. Do we need to add some sort of coding to change the format of the cell also to Text?

I am considering scrapping the idea entirely and writing separate macros to clean up certain sections of the data file before running this large macro. That might end up being better, because then the large macro will be more general and not specific to just this case.

 

[TommyGun]

Well, if you made the change to the KB article code as suggested, then the formatting of the cell shouldn't matter.

I'm leaving work, and will be away from a computer for a while, but I'll check in later.