Solved

converting minutes

Posted on 2008-06-13
25
343 Views
Last Modified: 2012-06-27
I have a vb module that exports multiple queries to a formatted excel workbook. on one column i would like to convert the minutes in those cells to hh:mm. The number of rows/cells will always be different and I dont want to have to manually do formatting. How can i do this in the VB module??
0
Comment
Question by:pgmtkl
  • 13
  • 12
25 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 21781757
Assuming you have referenced the Excel object model:

   TargetCells.NumberFormat = "[h]:mm"

Kevin
0
 

Author Comment

by:pgmtkl
ID: 21781812
sorry im not familirar with that would that be in a dim statement?
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 21781844
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
0
 

Author Comment

by:pgmtkl
ID: 21781906
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
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 21781983
  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
0
 

Author Comment

by:pgmtkl
ID: 21782070
I get user defined type not found. Does it make a diff if i am running this from access?
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 21782100
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
0
 

Author Comment

by:pgmtkl
ID: 21782139
now i get invalid or unqualified reference? do i need to add something additional. thx for your help
0
 

Author Comment

by:pgmtkl
ID: 21782146
that unqualified reference is on  .Range ??
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 21782149
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
0
 

Author Comment

by:pgmtkl
ID: 21782198
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?
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 21782246
I was assuming you had defined and set xlObj to the Excel application object. Have you?

Kevin
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:pgmtkl
ID: 21782274
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
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 21782286
Yes.

Kevin
0
 

Author Comment

by:pgmtkl
ID: 21782363
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
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 21782443
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
0
 

Author Comment

by:pgmtkl
ID: 21782464
Duplicate declaration in current scope??
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 21782473
Probably xlObj. Remove mine or yours.

Kevin
0
 

Author Comment

by:pgmtkl
ID: 21782564
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?
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 21782603
At this point I don't know because I have no idea what the rest of code looks like. Can you post it?

Kevin
0
 

Author Comment

by:pgmtkl
ID: 21782619
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
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 21782655
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
0
 

Author Comment

by:pgmtkl
ID: 21782736
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?
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
ID: 21782800
Sub exportExcel()

    Dim rs As DAO.Recordset
    Dim xlObj As Object, Sheet As Object
    Dim xlFile As String
    Dim Cell As Range
   
    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
        For Each Cell In Worksheet.Range("D2", Worksheet.Range("D2").End(-4121)).Cells
            Cell.Value = Cell.Value / 60 / 24
            Cell.NumberFormat = "[h]:mm"
        Next Cell
    Next Worksheet
   
  'save the excel file
    xlObj.ActiveWorkbook.SaveAs "C:\Week.xls"
   
    Set Sheet = Nothing
    xlObj.Quit
    Set xlObj = Nothing

End Sub

Kevin
0
 

Author Comment

by:pgmtkl
ID: 21783005
WOrks perfect! Thanks for all of your help.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now