Results 1 to 3 of 3
  1. #1
    Join Date
    May 2005
    Posts
    125
    Provided Answers: 1

    Unanswered: Date Format Issue When Importing from Access to Excel via VBA Script

    Hey

    Not sure if this should be in form, but my process uses both Access & Excel.

    My problem is this a routine is run where data is stored within an access database, my system is set to UK settings, the date columns within the table are set to UK format, but when I run the code below

    Code:
    Sub GPRAG()
    strPath = ThisWorkbook.Path
    strConnection = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & _
    strPath & "\CostSavings.accdb"
    Set Conn = CreateObject("ADODB.Connection")
    Conn.Open strConnection
    Set rs = CreateObject("ADODB.recordset")
    
    'Gather the data stored within the table Tbl_A3_Result, and transfers it to the pipeline document
    
    strSQL = "SELECT [Tbl_A3_Result].Company, [Tbl_A3_Result].TechSpecialist, [Tbl_A3_Result].CSNo, [Tbl_A3_Result].Region, " & _
    "[Tbl_A3_Result].GrpNo, [Tbl_A3_Result].AccNumber, [Tbl_A3_Result].AccountName, [Tbl_A3_Result].ContactName, " & _
    "[Tbl_A3_Result].BSLContact, [Tbl_A3_Result].KAM, [Tbl_A3_Result].SKAM, [Tbl_A3_Result].RAG, [Tbl_A3_Result].Bearings, " & _
    "[Tbl_A3_Result].MechanicalPT, [Tbl_A3_Result].FluidPower, [Tbl_A3_Result].Gearbox, [Tbl_A3_Result].Motors, " & _
    "[Tbl_A3_Result].Tools_GM, [Tbl_A3_Result].Seals, [Tbl_A3_Result].IndustrialAuto, [Tbl_A3_Result].ProjectStartDate, " & _
    "[Tbl_A3_Result].ProjectName, [Tbl_A3_Result].ProjectDetails, [Tbl_A3_Result].ProjectSummary, [Tbl_A3_Result].A3Approved, " & _
    "[Tbl_A3_Result].CSFV, [Tbl_A3_Result].CSFCD, [Tbl_A3_Result].A3Status, " & _
    "[Tbl_A3_Result].CSCD,[Tbl_A3_Result].CSV, [Tbl_A3_Result].ProjectStatus FROM [Tbl_A3_Result] "
    
    strSQL = strSQL
    
    rs.Open strSQL, Conn
    RowNo = 9
    Do Until rs.EOF = True
        For ColNo = 1 To 31
        Select Case rs.Fields(ColNo - 1).Name
        Case "CSCD", "CSFCD", "ProjectStartDate"
        PRAG.Cells(RowNo, ColNo) = Format(Trim(Mid(rs(ColNo - 1), 1, 32767)), "dd/mm/yyyy")
        Case Else
        PRAG.Cells(RowNo, ColNo) = Trim(Mid(rs(ColNo - 1), 1, 32767))
        End Select
        Next
        RowNo = RowNo + 1
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    Conn.Close
    Set Conn = Nothing
    End Sub
    to extract the data from the access database into an excel template albeit I also format various date fields within the code to "dd/mm/yyyy", but in the excel sheet the date is formatted US "mm/dd/yyyy"

    Any guidance would be great

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    instead of formatting as dd/mm/yyyy I'd suggest using dd mmm yyyy
    VBA is US centric so it tends to default to US date formats

    check the internationalistion of your MS Office installation
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2005
    Posts
    125
    Provided Answers: 1
    I'll give it whirl

    That's the ticket, spot on

    Thanks Healdem
    Last edited by MarkWhyte; 09-12-14 at 07:34.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •