UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Save As Error Workbook Class Failed, Access 2016    
 
   
blueman2
post Jun 14 2019, 11:00 AM
Post#1



Posts: 219
Joined: 15-November 14



Trying to create and save a workbook as csv.

This code worked perfectly until I got a new pc. Now running a ssd as C drive and data is on new "D:". I tried changing the drive letter but that didn't work.
I also upgraded to the current office 365 desktop version of excel and access from a 2013 version. Those are the only changes. Any help would be appreciated. Thanks (I'm not well versed in coding but this code did work)



CODE
'Save file in provided path
sFullFilePath = "C:\Users\" & Environ("Username") & "\A&S Crafted Products\A&S Crafted Products Home Page - Documents\Shipping\Midwest\Outgoing Midwest Shipping Orders_" & Format(Date, "MMDDYYYY") & ".csv"



    xlApp.Cells.Columns.AutoFit
    xlWorkbook.SaveAs sFullFilePath, 6
Go to the top of the page
 
DanielPineault
post Jun 14 2019, 11:23 AM
Post#2


UtterAccess VIP
Posts: 6,716
Joined: 30-June 11



Where is xlWorkbook declared and set? Can you post your full proc please.
Does the path component of sFullFilePath exist? You should test before running the SaveAs.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
blueman2
post Jun 14 2019, 03:02 PM
Post#3



Posts: 219
Joined: 15-November 14



Here's the entire code. As I mentioned, everything in this code worked perfectly until:

1)I started using a new PC with the C drive now being an ssd drive that houses the operating system only.The data and path for this folder resides now on what is named the D drive
2)I upgraded to the current desktop version of Office from a desktop version of Office 2013

Nothing else has changed.



CODE
Private Sub Submit_All_Shiipments_To_NAL_Click()
    'Declare variables
    Dim xlApp As Object
    Dim xlWorkbook As Object
    Dim rs As Recordset
    Dim lngFieldsCounter As Long
    Dim sFullFilePath As String
    
    'Assign values
    lngFieldsCounter = 0
        
    'Check Customer and Component is not mapped
    Set rs = CurrentDb.OpenRecordset("Select * from [qrySubmitShipmentNAL] where [CustomerID] IS NULL OR [CompCode] IS NULL")
    If Not rs.EOF Then
        MsgBox "Customer or Component is not mapped properly. Please review all the orders before sending to shipper!", vbOKOnly + vbInformation
        rs.Close
        Set rs = Nothing
        Exit Sub
    Else
        rs.Close
        Set rs = Nothing
        
        'Create a excel file and export Orders
        Set xlApp = CreateObject("Excel.Application")
        xlApp.Workbooks.Add
        Set xlWorkbook = xlApp.ActiveWorkbook
        'Update sheet name
        'xlApp.Sheets(1).Name = "NAL Report"
        xlApp.screenupdating = True
        xlApp.Visible = False
        xlApp.UserControl = True
        
        Set rs = CurrentDb.OpenRecordset("Select * from qrySubmitShipmentNALFinal")
        
        'Populate headers
        For lngFieldsCounter = 0 To rs.Fields.Count - 1
            xlApp.Worksheets(1).Cells(1, lngFieldsCounter + 1) = rs.Fields(lngFieldsCounter).Name
        Next lngFieldsCounter
        
        'Copy data from access query to excel
        xlApp.Worksheets(1).Range("A2").CopyFromRecordset rs
        rs.Close
        Set rs = Nothing
        
        'Save file in provided path
        'Save file in provided path
         sFullFilePath = "C:\Users\" & Environ("Username") & "\Company Name\Company Home Page - Documents\Shipping\Mideast\Outgoing Mideast Shipping Orders_" & Format(Date, "MMDDYYYY") & ".csv"
        
        xlApp.Cells.Columns.AutoFit
        xlWorkbook.SaveAs sFullFilePath, 6
                
        xlApp.Visible = True
        
        'Update shipping status and timestamp
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "qryUpdateShipmentStatusNAL"
        DoCmd.SetWarnings True
        
        Me.Requery
        MsgBox "Midwest Report Generated!", vbOKOnly + vbInformation
    End If
End Sub

Go to the top of the page
 
cheekybuddha
post Jun 14 2019, 03:40 PM
Post#4


UtterAccess VIP
Posts: 11,419
Joined: 6-December 03
From: Telegraph Hill


What's the new path on the D: dive where you want to save the csv file?

It's unlikely to still contain your pc user folder which will likely still be under C:

Tell us the path where you want to store the file and we'll help you change the code.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
DanielPineault
post Jun 14 2019, 03:43 PM
Post#5


UtterAccess VIP
Posts: 6,716
Joined: 30-June 11



What cheekybuddha said, please supply us with the full path from your system where you'd like to generate the csv.

Is this db only used by you, do you need to now add a handler to adjust the path based on the current user? If you need to add a handler, then what is your username so we can build an if statement.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
blueman2
post Jun 15 2019, 04:10 PM
Post#6



Posts: 219
Joined: 15-November 14



Thanks guys. I was sure I tried it without "users" but apparently I didn't. This is the path

D:\Mike\Company Name\Company Home Page - Documents\Shipping

That allowed me to save the file but yes, I will need a code change to allow another user in a remote location to use the database. He has a single C: Drive. So there has to be some kind of change that will allow for a different drive letter and a "user" in the path.

Thanks again

Go to the top of the page
 
gemmathehusky
post Jun 15 2019, 04:58 PM
Post#7


UtterAccess VIP
Posts: 4,723
Joined: 5-June 07
From: UK


wouldn't you need a D: folder structure that had a folder called D:\Users\Mike …..

Anyway, surely you could replace D:\Mike\ with C:\Users\Mike\ in the filename (replace command)

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
DanielPineault
post Jun 15 2019, 06:06 PM
Post#8


UtterAccess VIP
Posts: 6,716
Joined: 30-June 11



You could try something along the lines of
CODE
Private Sub Submit_All_Shiipments_To_NAL_Click()
    'Declare variables
    Dim xlApp                 As Object
    Dim xlWorkbook            As Object
    Dim db                    As DAO.Database
    Dim rs                    As DAO.Recordset
    Dim lngFieldsCounter      As Long
    Dim sPath                 As String
    Dim sFullFilePath         As String

    On Error GoTo Error_Handler

    'Assign values
    lngFieldsCounter = 0

    'Check Customer and Component is not mapped
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM [qrySubmitShipmentNAL] WHERE [CustomerID] IS NULL OR [CompCode] IS NULL")
    If Not rs.EOF Then
        MsgBox "Customer or Component is not mapped properly. Please review all the orders before sending to shipper!", vbOKOnly + vbInformation
    Else
        rs.Close

        'Create a excel file and export Orders
        Set xlApp = CreateObject("Excel.Application")
        xlApp.Workbooks.Add
        Set xlWorkbook = xlApp.ActiveWorkbook
        'Update sheet name
        'xlApp.Sheets(1).Name = "NAL Report"
        xlApp.screenupdating = True
        xlApp.Visible = False
        xlApp.UserControl = True

        Set rs = db.OpenRecordset("SELECT * FROM qrySubmitShipmentNALFinal")

        'Populate headers
        For lngFieldsCounter = 0 To rs.Fields.Count - 1
            xlApp.Worksheets(1).Cells(1, lngFieldsCounter + 1) = rs.Fields(lngFieldsCounter).Name
        Next lngFieldsCounter

        'Copy data from access query to excel
        xlApp.Worksheets(1).Range("A2").CopyFromRecordset rs

        'Save file in provided path
        If Environ("Username") = "Mike" Then
            sPath = "D:\Mike\Company Name\Company Home Page - Documents\Shipping\Mideast\"
        Else
            sPath = "C:\Users\" & Environ("Username") & "\Company Name\Company Home Page - Documents\Shipping\Mideast\"
        End If
        sFullFilePath = sPath & "Outgoing Mideast Shipping Orders_" & Format(Date, "MMDDYYYY") & ".csv"

        xlApp.Cells.Columns.AutoFit
        xlWorkbook.SaveAs sFullFilePath, 6

        xlApp.Visible = True

        'Update shipping status and timestamp
        db.Execute "qryUpdateShipmentStatusNAL", dbFailOnError

        Me.Requery
        MsgBox "Midwest Report Generated!", vbOKOnly + vbInformation
    End If

Error_Handler_Exit:
    On Error Resume Next
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    If Not db Is Nothing Then Set db = Nothing
    If Not xlWorkbook Is Nothing Then Set xlWorkbook = Nothing
    If Not xlApp Is Nothing Then Set xlApp = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Submit_All_Shiipments_To_NAL_Click" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
blueman2
post Jun 19 2019, 12:14 PM
Post#9



Posts: 219
Joined: 15-November 14



Thanks Daniel. Thad did it
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th July 2019 - 05:06 PM