Recordset and formatRows in Columns?

[silentwolf]

Hi guys,
I am trying to export from Access to Excel ...
All is working but now I am not sure how to go further..

I like to format Column C;D;E, in hh:mm after after the Header so all Rows from C2,D2,E2 to end of rows in hh:mm format.

I got the transfer worked out but not sure how to work with the columns..

Sub HoleDatenVonExcel()
    Dim appExcel As Excel.Application
    Dim wbkExcel As Excel.Workbook
    Dim wksExcel As Excel.Worksheet
    Dim rngExcel As Excel.Range
    
    Dim rcsExport As Recordset
    Dim fldExport As Field
    
    Dim intSpalte As Integer
    Dim lngRow As Long
    
    
    Set rcsExport = CurrentDb.OpenRecordset("tblObjMitarbeiter")
    
    Set appExcel = HoleAnwendung("Excel.Application")
    If appExcel Is Nothing Then
        MsgBox "Kein Excel gefunden!,", vbCritical, p_cstrAppTitel
    Else
        appExcel.Visible = True
        Set wbkExcel = appExcel.Workbooks.Add()
        Set wksExcel = wbkExcel.Worksheets(1)
        
'Spalten werden formatiert
        For intSpalte = 1 To rcsExport.Fields.Count
            With wksExcel.Cells(1, intSpalte)
                .Value = rcsExport.Fields(intSpalte - 1).Name
                .Interior.Color = vbGreen
            End With
        Next
        
[B]''How do I need to change this ?? So it will format each of the rows after Row 1 in the hh:mm format[/B]
'        For lngRow = 1 To rcsExport.Fields.Count
'            With wksExcel.Cells(1, lngRow)
'                .Value = rcsExport.Fields(lngRow - 1).Item
'                .Interior.Color = vbGreen
'            End With
'        Next
        
        
        Set rngExcel = wksExcel.Range("A2")
        rngExcel.CopyFromRecordset rcsExport
    End If
    appExcel.Visible = True
    
End Sub

Could someone let me know please.. I am sure it is not that difficult but I cant seam to get my head around this..


Thanks for looking!

Albert

 

[james_lankford]

...

I like to format Column C;D;E, in hh:mm after after the Header so all Rows from C2,D2,E2 to end of rows in hh:mm format.

..

start excel
put some data in C2 D2 E2, C3 D3 E3, C4 D4 E4
from the menu choose
Developer / Record Macro
now select columns C D E
format the columns the way you want
then select
"stop recording macro"
now look at the macro vba code

change the little you need to change (you might need to change sheet name of something

that's pretty much how you should do everything in excel --- record the macro and adjust it, then put the code into access

when I do that Excel generates this code for me

Option Explicit


Sub Macro1()
'
' Macro1 Macro
'


'
    Columns("C:E").Select
    Selection.NumberFormat = "hh:mm"
End Sub

 

[silentwolf]

Hi James,

thanks for your reply! I understand what you mean but I was wondering to do it straigth with the recordset... Here I am not quite sure how to do it.
But it is a start I will try )

Thanks again!

 

[james_lankford]

yeah, but you don't want to do it row by row
that's way too slow

you already have this code
Set wbkExcel = appExcel.Workbooks.Add()
Set wksExcel = wbkExcel.Worksheets(1)


so just add this
wksExcel.Columns("C:E").NumberFormat = "hh:mm"

 

[silentwolf]

Hi James,
Many thanks! I knew this must be easy but could not get it..
However the format is not right...
In access I have the field property set to Time 24 h ... so each of the fields show 17:00 .. when I export this file into excel it gives me a complete wrong date...
So I guess it must be someone converted so excel can reconize this format.

I have limmited time but will try bit further...

Thanks for your tip!

Albert