Trouble with Time Formats and Exporting Data

[Brutality]

Greetings,

I have the same problem as outlined here (Office version is 2000):

http://en.allexperts.com/q/Microsoft-Word-1058/2008/2/Mail-merge-time-field.htm

the only difference being I am attempting Mailmerge from a spreadsheet (as opposed to an Access database) with fields in a time format. I have tried changing to custom and using hh:mm:ss. I know about switches [http://www.gmayor.com/formatting_word_fields.htm]
but that does not solve the problem either.

After googling some more it seems this is rather uncommon but not unheard of and there aren't any obvious solutions.

So my question pertaining to Excel is this; is there some way of taking a time field and capturing the values as text or general while retaining the proper output (obviously when I simply change the format the values change to decimals so I am thinking if this is possible it might require VB code). I should point out the time values have been generated like so:

cell A2: =IF(A2="","","9:15:00")
cell A3: =IF(A3="","",B2+"00:00:30")
cell A4: =IF(A4="","",B3+"00:00:30")

which automatically sets the format to time. Any ideas? All help appreciated.

TIA
Mark

 

[AlanAnderson]

Hi,

I'm pretty new to VBA but I tried the following which may solve your problem

I saved a date and time into Cell W7 as date
This code then writes that date as a numeric into W8
It then writes the numeric value from W8 into W10

As I end with same date and time as I started with it obviously does work.
How youi would apply this to your case, I really don't know.
Hope this of some help.

Regards,

Alan

Sub TimeTest()
    Sheets("Sheet1").Cells(8, 23) = Format(Sheets("Sheet1").Cells(7, 23), "#,##0.00;[Red] (#,##00.00)")
    Sheets("Sheet1").Cells(10, 23) = Format(Sheets("Sheet1").Cells(8, 23), "dd-mm-yy h -mm-ss")
End Sub

 

[Brutality]

Thanks Alan,

That does *work*, in as far as the VB code runs and the result is correct in terms of the data integrity. But the format (at least in my version of Excel) is still the same as the original time data. Unfortunately, perhaps given the 'formatting' of the cell using VB, Excel is still treating it as a time (and I end up with the same result in my mail merge).

I appreciate the effort however.

Mark

 

[AlanAnderson]

Hi Mark,
Perhaps I was too literal.
If when saving or creating a variable you format it as numeric and then when reading it you format result as date that can work.

eg Var1 = format(VDateVar,"#,##0.00;[Red] (#,##00.00)")

FinalResult= format(Var1, "dd-mm-yy h -mm-ss")

or Vise versa.

Regards,

Alan

 

[Brutality]

Alan,

Thanks again. Yes I think I understand what you are saying in terms of reading it out. But AFAIK that does still leave me with the original Mailmerge problem, as the merge needs to take the time data from an excel column and I am limited by the switches mentioned above with respect to formatting it (unless I am missing something, and I may well be).

adieu
Mark

 

[Brutality]

Ok so I have a working solution. I was using ODBC to connect to my datasource through word which meant I could access multiple sheets in the same file, thus giving me the ability to use different named ranges. It turns out if I simply export each of the relevant sheets I want to use for labels and save them as a separate file (automated using a macro) then I can use DDE to access the file through word, and everything is formatted in the merge correctly. Strange but true!

Mark