Results 1 to 6 of 6
  1. #1
    Lakshman is offline Novice
    Windows XP Office 365
    Join Date
    Mar 2021
    Posts
    1

    Please help me where i am going wrong : Run time Error-3704

    Public Sub ExportTaxWorkforceData(strPeriod As String, strPath As String)
    Dim strExcelFile As String
    Dim xlApp As Excel.Application
    Dim xlWb As Excel.Workbooks
    Dim xlWs As Excel.Worksheet
    Dim rngCurr As Excel.Range
    Dim rstAsADODB.Recordset
    Dim strQuery As String
    Dim strSQL As String

    On Error GoToErrorHandler

    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Open("" &strPath& "")
    Set xlWs = xlWb("SetDates")
    xlApp.Visible = True
    xlWs.Range("B2").FormulaR1C1 = CInt(strPeriod)
    Call Sleep(10000)

    strQuery = "Learning Count"
    strSQL = "SELECT * FROM [" &strQuery& "];"
    Set rst = New ADODB.Recordset
    rst.OpenstrSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    Set xlWs = xlWb.Worksheets("Learning")
    Set rngCurr = xlWs.Range("Testing")
    rngCurr.CopyFromRecordsetrst
    Set rngCurr = Nothing
    Set rst = Nothing
    Call Sleep(10000)


    -------
    xlWb.Save
    xlWb.Close
    xlApp.Quit
    Set xlApp = Nothing
    Set xlWb = Nothing
    Set xlWs = Nothing
    Set rst = Nothing

    MsgBox "DONE!!!"

    ExitRoutine:


    Exit Sub

    ErrorHandler:
    If Not xlApp Is Nothing Then
    xlApp.Visible = True
    Set xlApp = Nothing
    End If
    If Not xlWb Is Nothing Then
    Set xlWb = Nothing
    End If
    If Not xlWs Is Nothing Then
    Set xlWs = Nothing
    End If

    If Not rst Is Nothing Then
    rst.Close
    Set rst = Nothing
    Else
    End If

    MsgBoxErr.Description& " Error Number: " &Err.Number
    Resume ExitRoutine
    End Sub

    I am getting Run time Error 3704, Please let me know where i am going wrong

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    And runtime error 3704 is?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    @Lakshman - some tips to help you. You will generate more interest if you
    a)state the error number and message. There are thousands of them and speaking for myself, I get tired of looking them up for people.
    b) state which line generates the error. Pretty hard to pinpoint that from here.
    c) use code tags (# on forum post toolbar) with proper indentation for more than a few lines of code. There are lots of posts in the forum that will show you how much easier it is to read.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    FWIW I gave up waiting for a response and looked. :-(

    VBA-Excel error 3704- operation is not allowed when the object is closed


    This line does not look correct to me, so I suspect you have not opened any workbook?
    Code:
    Set xlWb = xlApp.Workbooks.Open("" &strPath& "")
    I would have thought all you would need is
    Code:
    Set xlWb = xlApp.Workbooks.Open(strPath)
    strpath should hold the FULL path to the file PLUS it's extension.

    I am going to go to my grave saying this , but walk through your code in the debug window and see what is happening and the values of the variables.?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I am going to go to my grave saying this , but walk through your code in the debug window and see what is happening and the values of the variables.?
    Maybe there is a T-shirt about this? If not, you could print and sell them.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Possibly , but it is what I used every time something did not work.
    I also learnt more when doing it as well, and was a able to spot what I call 'silly' errors, the ones that take an age to work out, and end up with a forehead slap to self, when you find the error.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

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

Similar Threads

  1. Replies: 5
    Last Post: 10-05-2020, 03:50 AM
  2. Replies: 3
    Last Post: 07-18-2015, 05:02 PM
  3. VBA If getting error because I know it is wrong
    By Ruegen in forum Programming
    Replies: 8
    Last Post: 10-27-2013, 09:34 PM
  4. Summing Time is giving me wrong totals.
    By Nuke1096 in forum Access
    Replies: 7
    Last Post: 06-13-2013, 11:00 AM
  5. Replies: 1
    Last Post: 12-20-2011, 03:32 PM

Posting Permissions

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