1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved Opening Access File Exported that has Time Stamp

Discussion in 'Business Applications' started by flovettsr, Oct 20, 2018.

Thread Status:
Not open for further replies.
Advertisement
  1. flovettsr

    flovettsr Thread Starter

    Joined:
    Apr 1, 2011
    Messages:
    25
    Hello, I've created several Queries for my co-workers; however, there is still too much manual intervention. I have the file go to a folder automatically now as an excel file. I would like to name this file, so that I can open it and run an excel Macro on it. Here is the file info that is being exported. Thanks in advance.

    I would like to open this file, it includes a time stamp, hence the question.

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "DARDEN_RECENTCALLS", _
    "G:\National Accounts\Procedure & Systems\Access Database\P&S Database Suite\Database_SourceData\DARDEN STUFF FROM ACCESSS\DardenCalls_Yest&30daysToo " & (Format(Now(), "mm_dd_yyyy hh mm ss AMPM ")) & "xlsx"
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    I don't quite understand what you mean.
    When do you want to open this file, the moment you create it?
     
  3. flovettsr

    flovettsr Thread Starter

    Joined:
    Apr 1, 2011
    Messages:
    25
    I'm sorry I didn't make myself clear. I've given it further thought and I would like to, if possible, save it to the desired folder and open it also. Hope that makes things clearer
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    Something like this:

    Code:
    Public Sub transfer2Excel()
    Dim wb      As Object
    Dim xl      As Object
    Dim expPath As String
    Dim ExcelWB As String
    Set xl = CreateObject("excel.application")
    Dim ret As Byte
    expPath = "G:\_Forii\18-10-Oct\flovettsr\"
    ExcelWB = "DardenCalls_Yest30daysToo" & (Format(Now(), "mm_dd_yyyy hh mm ss AMPM ")) & "xlsx"
    On Error GoTo ErrHandler
    Kill expPath & ExcelWB
    ret = 1
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Lottery_List", expPath & ExcelWB, True
    ret = 0
    Set wb = xl.Workbooks.Open(expPath & ExcelWB)
    wb.Visible = True
    ErrHandler:
    End Sub
    
    You probably have to add some references in the Tools options
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    Minor correction:
    Code:
    Public Sub transfer2Excel()
    Dim xl      As Object
    Dim expPath As String
    Dim ExcelWB As String
    Set xl = CreateObject("excel.application")
    Dim ret As Byte
    expPath = "G:\_Forii\18-10-Oct\flovettsr\"
    ExcelWB = "DardenCalls_Yest30daysToo" & (Format(Now(), "mm_dd_yyyy hh mm ss AMPM"))
    On Error Resume Next
    Kill expPath & ExcelWB
    ret = 1
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Lottery_List", expPath & ExcelWB, True
    ret = 0
    xl.Workbooks.Open FileName:=expPath & ExcelWB & ".xlsx"
    xl.Visible = True
    Err.Clear
    On Error GoTo 0
    End Sub
    
     
  6. flovettsr

    flovettsr Thread Starter

    Joined:
    Apr 1, 2011
    Messages:
    25
    Thanks, I made a few changes as you know to fit where the file is actually located. It worked Perfectly.
    You're Awesome!!!!!!!
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,597
    First Name:
    Hans
    Happy to have been able to help, Access is not my strong :)
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1218124

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice