?
Solved

converting minutes

Posted on 2008-06-13
25
Medium Priority
?
351 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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
 

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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

777 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