Page 1 of 3 123 LastLast
Results 1 to 15 of 43
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro Code ERROR (Office2000)

    I've got the following code in a macro which saves the content of the current file as BCPfollowed by todays date.
    The problem Im having with this code is that it opens a document which I've saved using this macro at the start automatically.

    Can anyone tell me why this is happening and how I could prevent it from happening?
    Sub SFMSaveByDate()
    Dim strFileName As String
    strFileName = "BCP" & Format(Now, "DDMMYY") & ".xls"
    ActiveWorkbook.SaveAs Filename:="S:SRI_WORK_AREADocuments" & strFileName, _
    FileFormat:=xlWorkbookNormal

    End Sub

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Code ERROR (Office2000)

    This code does not open anything, therefore it is not directly the cause of another file being opened.

    Do you have a button that is linked to this macro in the other workbook (the other document is a workbook isn't it?)?
    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Code ERROR (Office2000)

    This macro is on the menu bar of Excell, that makes the answer for your question: yes. But then its on all the documents I save using this button, The question is why does it only open this particular document?

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Code ERROR (Office2000)

    What is this "document"? Is the command on the menu linked to a macro in this document?
    Legare Coleman

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro Code ERROR (Office2000)

    What is the name of the workbook being opened? Your code takes the active workbook and saves it as "BCP<DDMMYY>.xls" without regard to whatever was the name of the document when it was opened. Can you post the workbook that you usually open and tell us why you open it rather than opening the last version of "BCP<DDMMYY>.xls"?
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Code ERROR (Office2000)

    Ok, What Im doing is exporting data from a query and creating reports everyday, that is why I don't open the last version of the BCP<DDMMYY>.xls. The macro is in the workbook where Im exporting the data and these data are saved everyday for future use. The Name of the workbook which is being opened whever I run this query is called BCP210901.xls.
    Attached Files Attached Files

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro Code ERROR (Office2000)

    The workbook you posted doesn't contain any code. Perhaps you could post a censored copy of the workbook which contains the code which exports the data? Where does the file save code reside?
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Code ERROR (Office2000)

    The following is the visual basic code I use to export the contents of the query into the spreadsheet workbook.

    Function SFMReport()
    On Error GoTo ExportSFMReport_Err
    Dim rst As DAO.Recordset, db As DAO.Database
    ' Turn System warnings off
    DoCmd.SetWarnings False
    ' Delete contents of the table
    DoCmd.RunSQL "DELETE [tblSFMReportSource].* FROM [tblSFMReportSource] WITH OWNERACCESS OPTION;", 0
    ' Run Append query to add SFM records to the table
    DoCmd.OpenQuery "AppendToSFMReportSource", acNormal, acEdit
    ' Turn System warnings on.
    DoCmd.SetWarnings True
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblSFMReportSource")
    If rst.BOF And rst.EOF Then
    MsgBox "There are no records", vbOKOnly
    Set rst = Nothing
    Set db = Nothing
    'Open fax cover
    Set objWord = CreateObject("Word.Basic")
    objWord.AppShow
    'objWord.AppMaximize "", 1 (optional)
    objWord.FileOpen "S:SRI_WO~1TRADEA~1Fax.doc"
    Exit Function
    Else
    ' Export records to spreadsheet and open it
    DoCmd.OutputTo acOutputQuery, "SFMTradeReport", acFormatXLS, "S:SRI_WORK_AREATRADEA~1TRADES~2.XLS", True
    ' Display message
    Beep
    MsgBox "Data has been exported successfully.", vbInformation, "Export Confirmation"
    ' Delete contents of the table
    DoCmd.RunSQL "DELETE [tblSFMReportSource].* FROM [tblSFMReportSource] WITH OWNERACCESS OPTION;", 0
    Set rst = Nothing
    Set db = Nothing
    End If
    Exit Function

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro Code ERROR (Office2000)

    So the spreadsheet file you are saving is called: TRADES~2.XLS?
    from
    DoCmd.OutputTo acOutputQuery, "SFMTradeReport", acFormatXLS, "S:SRI_WORK_AREATRADEA~1TRADES~2.XLS", True

    And TRADES~2.XLS is the one you want to save as BCP<MMDDYY>.XLS? What do you do with TRADES~2.XLS?
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Code ERROR (Office2000)

    I don't do anything with that. It is just a temporary file which is used for exporting. Thats all.

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro Code ERROR (Office2000)

    It is the target file resulting from the export? Is there a reason you don't directly save this file as BCP<MMDDYY>.xls? Where does the file save code reside - in a file in ...XLStart?
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Code ERROR (Office2000)

    I want to export it everyday, and I have no knowledge in visual basic, so I've used a macro and converted it into visual basic code and with someones help got it to do what I exactly wanted it to do. Why?, is there any problem there?

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro Code ERROR (Office2000)

    There's no problem, I'm just trying to understand your process and objective . Since I don't have your source data, LAN environment, etc., it's hard to be certain that this will work for you, but try the following changes to your data export-to-spreadsheet code:

    To the beginning of the Function SFMReport() code add:

    Dim strFileName As String

    Edited for line wrapping

    ... and change this line:
    DoCmd.OutputTo acOutputQuery, "SFMTradeReport", _
    acFormatXLS, "S:SRI_WORK_AREATRADEA~1TRADES~2.XLS", True

    to these three lines:
    strFileName = "BCP" & Format(Now, "DDMMYY") & ".xls"
    DoCmd.OutputTo acOutputQuery, "SFMTradeReport", _
    acFormatXLS, "S:SRI_WORK_AREATRADEA~1" & strFileName, True
    Workbooks(strFileName).Save

    If this code works, you will not need the separate file save code which was the subject of your original question.

    Also, I would rename this procedure to "Sub SFMReport()", instead of calling it a Function, since it doesn't return a value, is not called and isn't used as a spreadsheet function.
    -John ... I float in liquid gardens
    UTC -7ąDS

  14. #14
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Code ERROR (Office2000)

    it gives me an error message: "Sub or function not defined"
    Sub SFM()
    Dim strFileName As String
    'End Sub
    'On Error GoTo ExportSFMReport_Err
    Dim rst As DAO.Recordset, db As DAO.Database
    ' Turn System warnings off
    DoCmd.SetWarnings False
    ' Delete contents of the table
    DoCmd.RunSQL "DELETE [tblSFMReportSource].* FROM [tblSFMReportSource] WITH OWNERACCESS OPTION;", 0
    ' Run Append query to add SFM records to the table
    DoCmd.OpenQuery "AppendToSFMReportSource", acNormal, acEdit
    ' Turn System warnings on.
    DoCmd.SetWarnings True
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblSFMReportSource")
    If rst.BOF And rst.EOF Then
    MsgBox "There are no records", vbOKOnly
    Set rst = Nothing
    Set db = Nothing
    'Open fax cover
    Set objWord = CreateObject("Word.Basic")
    objWord.AppShow
    'objWord.AppMaximize "", 1 (optional)
    objWord.FileOpen "S:SRI_WO~1TRADEA~1Fax.doc"
    Exit Sub
    Else
    ' Export records to spreadsheet and open it
    strFileName = "BCP" & Format(Now, "DDMMYY") & ".xls"
    DoCmd.OutputTo acOutputQuery, "SFMTradeReport", acFormatXLS, "S:SRI_WORK_AREATRADEA~1" & strFileName, True
    Workbooks(strFileName).Save
    ' Display message
    Beep
    MsgBox "Data has been exported successfully.", vbInformation, "Export Confirmation"
    ' Delete contents of the table
    DoCmd.RunSQL "DELETE [tblSFMReportSource].* FROM [tblSFMReportSource] WITH OWNERACCESS OPTION;", 0
    Set rst = Nothing
    Set db = Nothing
    End If
    Exit Sub

  15. #15
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro Code ERROR (Office2000)

    When the error occurs, and you get the error message, click on Debug and tell us which line is highlighted in yellow.
    -John ... I float in liquid gardens
    UTC -7ąDS

Page 1 of 3 123 LastLast

Posting Permissions

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