none
Export a Query, Format and save as CSV RRS feed

  • Question

  • Hello,

    I am trying to adjust the code below (that works) so I end up with a .csv instead of a .xls

    Each attempt to replace the .xls with .csv resulted 
    Run-time error '3027'
    Cannot update, Database or Object is read only.

    Could someone help me figure out what i am missing please?

    Private Sub Command12_Click()
    
    
    Dim stamp As String
    stamp = Format(Now, "yyyymmddhhnnss")
    
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryPO_ImportInJS", "TEST.xls", True
    Call ModifyExportedExcelFileFormats("TEST.xls", "qryPO_ImportInJS")
    End Sub
    
    
    
    Public Sub ModifyExportedExcelFileFormats(sFile As String, sSheet As String)
    On Error GoTo Proc_Error
    
        Dim xlApp As Object
        Dim xlSheet As Object
    
    Set xlApp = CreateObject("Excel.Application")
    Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)
        
    With xlApp
        .Application.Rows("1:1").Select
        .Application.Selection.Delete Shift:=xlUp
        .Application.Rows("1:5").Select
        .Application.Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        .Application.Range("A1").Select
        .Application.ActiveCell.FormulaR1C1 = "Amazon"
        .Application.Range("I6").Select
        .Application.Selection.Copy
        .Application.Range("A3").Select
        .Application.ActiveSheet.Paste
        .Application.Range("G6").Select
        .Application.Application.CutCopyMode = False
        .Application.Selection.Copy
        .Application.Range("A4").Select
        .Application.ActiveSheet.Paste
        .Application.Range("H6").Select
        .Application.Application.CutCopyMode = False
        .Application.Selection.Copy
        .Application.Range("A5").Select
        .Application.ActiveSheet.Paste
        .Application.Columns("G:I").Select
        .Application.Application.CutCopyMode = False
        .Application.Selection.Delete Shift:=xlToLeft
        .Application.Range("A3").Select
        .Application.Selection.NumberFormat = "yyyy/mm/dd"
        .Application.Range("A1").Select
        .Application.ActiveWorkbook.Save
        .Application.ActiveWorkbook.Close
        .Quit
    End With
    
    MsgBox "File Saved in :....."
    
    
    Proc_Error:
    Set xlApp = Nothing
    Set xlSheet = Nothing
    
    
    End Sub

    Thursday, November 19, 2020 4:42 AM

All replies