Solved Opening Access File Exported that has Time Stamp

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

flovettsr

Thread Starter
Joined
Apr 1, 2011
Messages
26
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"
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
I don't quite understand what you mean.
When do you want to open this file, the moment you create it?
 

flovettsr

Thread Starter
Joined
Apr 1, 2011
Messages
26
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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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
 

flovettsr

Thread Starter
Joined
Apr 1, 2011
Messages
26
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
Thanks, I made a few changes as you know to fit where the file is actually located. It worked Perfectly.
You're Awesome!!!!!!!
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top