Results 1 to 6 of 6

Issue with simple Export to excel

  1. #1
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Location
    Ottawa, ON
    Posts
    56

    Issue with simple Export to excel

    OK there seems to be two issues. The first for some reason it won't always go to it's path not sure why.



    The second is when it does export it opens two excel spreadsheets. One with just the header and the other has header with data. I cannot spot the error.


    Sub ExportToExcel(strSQL As String, FilePath As String, sFileName As String)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim ExportDate As String
    Dim UserName As String
    Dim oExcel As Object
    Dim oBook As Object
    Dim oSheet As Object

    UserName = getOSUserName
    ExportDate = Format(Date, "mm_dd_yyyy") '& " " & Format(Time, "HH:MM:SS")
    ExportDate = Replace(ExportDate, ":", " ")

    sFileName = "MyTable.xls"

    ' FilePath = "U:" '"\\fltottwnt345\home"
    ' sFileName = sFileName & "_" & UserName & "_" & ExportDate & ".xls"

    FilePath = sFileName

    'Start a new workbook in Excel
    Set oExcel = CreateObject("Excel.Application")
    Set oBook = oExcel.Workbooks.Add

    Set oSheet = oBook.Worksheets(1) 'Work with the first worksheet

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)

    'IRec = rs.RecordCount
    rs.MoveFirst

    For iCols = 0 To rs.Fields.count - 1
    oSheet.Cells(5, iCols + 1).value = rs.Fields(iCols).Name
    Next
    With oSheet.Range(oSheet.Cells(5, 1), oSheet.Cells(5, rs.Fields.count))
    .Font.Bold = True
    .Font.ColorIndex = 5 '2
    .Interior.ColorIndex = 1
    .HorizontalAlignment = xlCenter
    End With

    Dim FieldName As String

    Dim row As Integer
    row = 6

    Do While Not rs.EOF
    For iCols = 1 To rs.Fields.count - 1
    FieldName = rs.Fields(iCols).Name
    oSheet.Cells(row, iCols).value = rs.Fields(iCols - 1)
    Next
    row = row + 1
    rs.MoveNext

    Loop


    oExcel.Workbooks.Open (FilePath)
    oExcel.visible = True
    'Set oSheet = Nothing 'disconnect from the Worksheet

    'oBook.SaveAs sFileName 'Save (and disconnect from) the Workbook
    oBook.Close
    Set oSheet = Nothing 'disconnect from the Worksheet
    Set oBook = Nothing
    oExcel.Quit 'Close (and disconnect from) Excel
    Set oExcel = Nothing

    rs.Close
    Set rs = Nothing
    End Sub

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,621
    why are you looping 1 record at a time, instead of exporting all at once using Transferspeadsheet?

  3. #3
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Location
    Ottawa, ON
    Posts
    56
    I will also be doing some formatting to the spreadsheet once I have the sub working. Right now only sometimes it exports and when it does it opens 2 spreadsheeets

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,621
    Transferspreadsheet ALWAYS works and then you can open and format.

  5. #5
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Location
    Ottawa, ON
    Posts
    56
    Yeah that isn't what the client wants. They want it formatted when they click export. But I got it working now with the formatting I wanted.


    Quote Originally Posted by ranman256 View Post
    Transferspreadsheet ALWAYS works and then you can open and format.

  6. #6
    engolm is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2019
    Posts
    1
    Quote Originally Posted by greatwhite View Post
    Yeah that isn't what the client wants. They want it formatted when they click export. But I got it working now with the formatting I wanted.
    Can you please share the code you used?

    Additionally does your query include criterias;

    Private Sub CMB_Excel_Click()




    Dim conn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim cmdCommand As New ADODB.Command
    Dim excelKitabi As Excel.Workbook
    Dim excelSayfasi As Excel.Worksheet
    Dim BaslamaAraligi As Excel.Range
    Dim sutun As Integer
    Dim baslik As Variant
    Set conn = CurrentProject.Connection
    With cmdCommand
    .ActiveConnection = conn
    .commandtext = "select * from qryCYPY"
    .CommandType = adCmdText
    End With
    Set conn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    With rst
    .Open cmdCommand
    End With
    Set myExcel = New Excel.Application
    Set excelKitabi = myExcel.Workbooks.Add
    Set excelSayfasi = excelKitabi.ActiveSheet
    myExcel.Visible = True
    sutun = 1
    With rst
    For Each baslik In .Fields
    With excelSayfasi
    .Cells(5, sutun).Value = baslik.Name
    .Cells(5, sutun).Interior.ColorIndex = 15
    .Cells(5, sutun).Interior.Pattern = xlSolid
    .Cells(5, sutun).Font.ColorIndex = 2
    .Cells(5, sutun).Font.Bold = True
    .Cells(1, 1).Value = "merhaba"
    sutun = sutun + 1
    End With
    Next
    End With
    Set BaslamaAraligi = excelSayfasi.Cells(6, 1)
    BaslamaAraligi.CopyFromRecordset rst
    excelSayfasi.columns("a:at").entirecolumn.autofit
    rst.Close
    Set rst = Nothing
    Set conn = Nothing
    Set myExcel = Nothing
    Exit Sub


    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Export To Excel with formatting issue
    By sdel_nevo in forum Programming
    Replies: 28
    Last Post: 12-15-2017, 05:36 AM
  2. Simple report export to excel
    By crombiecrunch in forum Reports
    Replies: 2
    Last Post: 05-30-2013, 09:10 AM
  3. excel link with access differs from simple export from access
    By M0RDANT in forum Import/Export Data
    Replies: 4
    Last Post: 03-25-2013, 02:43 PM
  4. Simple Export Not So Simple
    By jgelpi16 in forum Programming
    Replies: 11
    Last Post: 09-01-2010, 07:23 AM
  5. Export a simple query result to an excel file
    By wop_nuno in forum Import/Export Data
    Replies: 1
    Last Post: 05-21-2009, 04:18 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums