Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

converting minutes

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
pgmtkl
Asked:
pgmtkl
  • 13
  • 12
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
Assuming you have referenced the Excel object model:

   TargetCells.NumberFormat = "[h]:mm"

Kevin
0
 
pgmtklAuthor Commented:
sorry im not familirar with that would that be in a dim statement?
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
pgmtklAuthor Commented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
  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
 
pgmtklAuthor Commented:
I get user defined type not found. Does it make a diff if i am running this from access?
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
pgmtklAuthor Commented:
now i get invalid or unqualified reference? do i need to add something additional. thx for your help
0
 
pgmtklAuthor Commented:
that unqualified reference is on  .Range ??
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
pgmtklAuthor Commented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
I was assuming you had defined and set xlObj to the Excel application object. Have you?

Kevin
0
 
pgmtklAuthor Commented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
Yes.

Kevin
0
 
pgmtklAuthor Commented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
pgmtklAuthor Commented:
Duplicate declaration in current scope??
0
 
zorvek (Kevin Jones)ConsultantCommented:
Probably xlObj. Remove mine or yours.

Kevin
0
 
pgmtklAuthor Commented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
pgmtklAuthor Commented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
pgmtklAuthor Commented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
pgmtklAuthor Commented:
WOrks perfect! Thanks for all of your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 13
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now