[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

Featured Post

Industry Leaders: 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!

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