Tech Support Guy banner
Status
Not open for further replies.
1 - 7 of 7 Posts

·
Registered
Joined
·
27 Posts
Discussion Starter · #1 ·
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"
 

·
Trusted Advisor
Joined
·
6,911 Posts
I don't quite understand what you mean.
When do you want to open this file, the moment you create it?
 

·
Registered
Joined
·
27 Posts
Discussion Starter · #3 ·
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
 

·
Trusted Advisor
Joined
·
6,911 Posts
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
 

·
Trusted Advisor
Joined
·
6,911 Posts
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
 

·
Registered
Joined
·
27 Posts
Discussion Starter · #6 ·
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!!!!!!!
 

·
Trusted Advisor
Joined
·
6,911 Posts
Happy to have been able to help, Access is not my strong :)
 
1 - 7 of 7 Posts
Status
Not open for further replies.
Top