Exporting multiple sheets drops time from date/time

[KevH]

I have a WB with several sheets of log data. The first column in each sheet is a date/time stamp. I created a macro on my “Main” page that will cycle through the data sheets & export each as their own workbook.

The first page exports the date/time stamp correctly, but all the other sheets export with just a date stamp. On those exported sheets/WB’s, if I change the format back to date/time it shows all the times as “0:00”.

Can you tell me what I’m missing?

For Each ws In ThisWorkbook.Worksheets
OutputFile = "TrendData " & ws.Name & " " & StartMonth & StartDay & "-" & EndMonth & EndDay & ".xls"
If ws.Name <> "Main" Then
    Sheets(ws.Name).Select
    Sheets(ws.Name).Copy
    ActiveWorkbook.SaveAs Filename:=WeekPath & OutputFile, _
    FileFormat:=xlText, CreateBackup:=False
    ActiveWorkbook.Close
    ThisWorkbook.Activate
End If
Next

 

[Norie]

Where and how are you actually entering these date/time stamps?

It sounds to me that basically there is no time part in the other sheets.

By the way why not use the loop variable ws when copying?

ws.Copy
With ActiveWorkbook
       .SaveAs Filename:=WeekPath & OutputFile, _
    FileFormat:=xlText, CreateBackup:=False
       .Close
End With

Note there's no need to Activate.

 

[KevH]

Where and how are you actually entering these date/time stamps?

It sounds to me that basically there is no time part in the other sheets.

I'm using an Access macro to pull from SQL server & export to Excel. In Excel I'm reviewing the data in a single worksheet & then disiminating the data with the Excel macro. I'll double check the data I'm getting in Access & see if I missed anything.

By the way why not use the loop variable ws when copying?

Because I'm still fumbling in the dark.

Thanks for the tip. I'll be sure and let you know what I find & how the code suggestion wokrs out.

 

[Norie]

Can I ask why the 3 stages?

SQL Server to Access to Excel to seperate text files.

If you are already using code in Access couldn't you cut out the Excel 'middle-man' and export straight to text.

 

[KevH]

Yeah. I know it's less than ideal


Some context


The data is from building automation field devices recording HVAC trends. These devices are sometimes finicky, so I have to make sure that data is there and/or realistic. For example, if a temp sensor goes from 74 Deg F up to 250 Deg F, then either the building is on fire or the sensor is bad. (If it ever ends up the first case I’ll be sure a post a YouTube link.) If it goes from 74 to "", then we have some kind of commication issue.


The catches


The first catch is that I’m still working out the actual rules for this so I can’t entirely automate it.

The second catch is that I'm not allowed anything more than read access to some of the DB's on the server.

So, In Access, I have to link to the DB's/Tables I can, use a join to filter the data down to building, floor, and device (chiller, boiler, AC, etc), do a transform on the data (Each sheet is a floor listed by date/time and device), and then export the data.

I could export each building & floor by folder & WB, but that makes the visual QA step a pain (especially for the 26 floor building).


Date/Time


Regarding the date/time stamp it looks like it was pilot error. The sheets are being overwritten, but the formatting is not being reset. By default only the date is being displayed. So the sheets where I actually set the desired date/time stamp format are exporting with said date/time stamp format.

Now I can go research how to set the cell formatting in either Access VBA or Excel VBA to force it to display the full stamp when I open the file. Yay.