converting minutes

Assuming you have referenced the Excel object model:

   TargetCells.NumberFormat = "[h]:mm"

Kevin

sorry im not familirar with that would that be in a dim statement?

No, that's a property assignment. It assumes you have the Excel object model referenced and that you have the range of cells defined in the Range variable TargetCells. More specifically:

   Dim TargetCells As Range
   Set TargetCells = oExcelApp.Workbooks("Book1.xls").Worksheets("Sheet1").Range("A1:A100")
   TargetCells.NumberFormat = "[h]:mm"

Kevin

woudl this target the entire workbook and cells for a specific column? if i need all of column d on all sheets would i use something similara to below? I am not familrar with the formatting portions. I have 5 worksheets all would need the same for column d, just the row count will always be different.

Dim x
Dim TargetCells As Range
   Set TargetCells = oExcelApp.Workbooks("Book1.xls").Worksheets("Sheet1").Range("A1:A100")
   TargetCells.NumberFormat = "[h]:mm"
With xlObj
For x = 1 To .worksheets.Count
.sheets(x).Activate
.Range("A1", .Range("A1").End(xlToRight).End(xlDown)).Columns.AutoFit
.Range("A1", .Range("A1").End(xlToRight)).Font.Bold = True
.Range("A1", .Range("A1").End(xlToRight)).HorizontalAlignment = xlCenter

  Dim Worksheet As Worksheet
   For Each Worksheet In xlObj.Workbooks("Book1.xls").Worksheets
      Worksheet..Range("D2", .Range("D2").End(xlToRight).End(xlDown)).NumberFormat = "[h]:mm"
   Next Worksheet

Kevin

I get user defined type not found. Does it make a diff if i am running this from access?

Not if you have referenced the Excel object model. Also, I just noticed an error in the above code. Corrected:

  Dim Worksheet As Worksheet
   For Each Worksheet In xlObj.Workbooks("Book1.xls").Worksheets
      Worksheet.Range("D2", .Range("D2").End(xlToRight).End(xlDown)).NumberFormat = "[h]:mm"
   Next Worksheet

You can also try this if you don't want to reference the Excel object model:

  Dim Worksheet As Object
   For Each Worksheet In xlObj.Workbooks("Book1.xls").Worksheets
      Worksheet.Range("D2", .Range("D2").End(xlToRight).End(xlDown)).NumberFormat = "[h]:mm"
   Next Worksheet

Kevin

now i get invalid or unqualified reference? do i need to add something additional. thx for your help

that unqualified reference is on  .Range ??

Sorry...not thinking clearly :-(

  Dim Worksheet As Object
   For Each Worksheet In xlObj.Workbooks("Book1.xls").Worksheets
      Worksheet.Range("D2", Worksheet.Range("D2").End(xlDown)).NumberFormat = "[h]:mm"
   Next Worksheet

Kevin

ok, now i get it to run, but then i get '91 error. Object variable or With block variable not set on this  For Each Worksheet In xlObj.Workbooks("Week.xls").Worksheets

DOes somethng else need to be added?

I was assuming you had defined and set xlObj to the Excel application object. Have you?

Kevin

I dont think i do, do  i need to add this statement?

Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.Open "C:\Week.xls"
    With xlObj

Yes.

Kevin

I added the below and it runs and stops on worksheet.range line with error '1004' application defined error. Is something wrong with how i have it?

Dim Worksheet As Object
   Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.Open "C:\Week.xls"
    With xlObj
   For Each Worksheet In xlObj.Workbooks("Week.xls").Worksheets
      Worksheet.Range("D2", Worksheet.Range("D2").End(xlDown)).NumberFormat = "[h]:mm"
   Next Worksheet
End With

Try:

   Dim xlObj As Object
   Dim Workbook As Object
   Dim Worksheet As Object
   Set xlObj = CreateObject("Excel.Application")
   Set Workbook = xlObj.Workbooks.Open("C:\Week.xls")
   For Each Worksheet In Workbook.Worksheets
      Worksheet.Range("D2", Worksheet.Range("D2").End(-4121)).NumberFormat = "[h]:mm"
   Next Worksheet

Kevin

Duplicate declaration in current scope??

Probably xlObj. Remove mine or yours.

Kevin

When i remove one of the statements, it stops running says it can not access my week.xls workbook? DO i need to maybe rename on of the xlObj statements to xlObja?

At this point I don't know because I have no idea what the rest of code looks like. Can you post it?

Kevin

Here it is:

Sub exportExcel()
Dim rs As DAO.Recordset
Dim xlObj As Object, Sheet As Object
Dim xlFile As String
xlFile = "c:\MasterWk.xls"
    Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.Open xlFile
    xlObj.Visible = True
       
   
    Set rs = CurrentDb.OpenRecordset("Mon")
    Set Sheet = xlObj.activeworkbook.Worksheets("Mon")
    Sheet.Range("A5").CopyFromRecordset rs  
       
       
    Set rs = CurrentDb.OpenRecordset("Tue")
    Set Sheet = xlObj.activeworkbook.Worksheets("Tue")
    Sheet.Range("A5").CopyFromRecordset rs  
   
   
    Set rs = CurrentDb.OpenRecordset("Wed")
    Set Sheet = xlObj.activeworkbook.Worksheets("Wed")
    Sheet.Range("A5").CopyFromRecordset rs  
   
   
    Set rs = CurrentDb.OpenRecordset("Thu")
    Set Sheet = xlObj.activeworkbook.Worksheets("Thu")
    Sheet.Range("A5").CopyFromRecordset rs  
   
    Set rs = CurrentDb.OpenRecordset("Fri")
    Set Sheet = xlObj.activeworkbook.Worksheets("Fri")
    Sheet.Range("A5").CopyFromRecordset rs  
     
   
  'save the excel file
    xlObj.activeworkbook.saveas "C:\Week.xls"
   
    Set Sheet = Nothing
    xlObj.Quit
    Set xlObj = Nothing


   
  Dim xlObj As Object
   'Dim xlObj As Object
   Dim Workbook As Object
   Dim Worksheet As Object
   Set xlObja = CreateObject("Excel.Application")
   Set Workbook = xlObja.Workbooks.Open("C:\Week.xls")
   For Each Worksheet In Workbook.Worksheets
      Worksheet.Range("D2", Worksheet.Range("D2").End(-4121)).NumberFormat = "[h]:mm"
   Next Worksheet

End


'save the excel file
    xlObja.activeworkbook.saveas "C:\Week.xls"
   
    Set Sheet = Nothing
    xlObja.Quit
    Set xlObj = Nothing


End Sub

Try this:

Sub exportExcel()
Dim rs As DAO.Recordset
Dim xlObj As Object, Sheet As Object
Dim xlFile As String
xlFile = "c:\MasterWk.xls"
    Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.Open xlFile
    xlObj.Visible = True
       
   
    Set rs = CurrentDb.OpenRecordset("Mon")
    Set Sheet = xlObj.ActiveWorkbook.Worksheets("Mon")
    Sheet.Range("A5").CopyFromRecordset rs
   
       
    Set rs = CurrentDb.OpenRecordset("Tue")
    Set Sheet = xlObj.ActiveWorkbook.Worksheets("Tue")
    Sheet.Range("A5").CopyFromRecordset rs
   
   
    Set rs = CurrentDb.OpenRecordset("Wed")
    Set Sheet = xlObj.ActiveWorkbook.Worksheets("Wed")
    Sheet.Range("A5").CopyFromRecordset rs
   
   
    Set rs = CurrentDb.OpenRecordset("Thu")
    Set Sheet = xlObj.ActiveWorkbook.Worksheets("Thu")
    Sheet.Range("A5").CopyFromRecordset rs
   
    Set rs = CurrentDb.OpenRecordset("Fri")
    Set Sheet = xlObj.ActiveWorkbook.Worksheets("Fri")
    Sheet.Range("A5").CopyFromRecordset rs
   
    For Each Worksheet In xlObj.ActiveWorkbook.Worksheets
        Worksheet.Range("D2", Worksheet.Range("D2").End(-4121)).NumberFormat = "[h]:mm"
    Next Worksheet
   
  'save the excel file
    xlObj.ActiveWorkbook.SaveAs "C:\Week.xls"
   
    Set Sheet = Nothing
    xlObj.Quit
    Set xlObj = Nothing

End Sub

Kevin

well that works really good. but now when i go to the workbook the cell that used to have 120 that should display as 02:00 as 1440:00. Do i need to add some additional formats?

WOrks perfect! Thanks for all of your help.