help with export excel in vba! (1 Viewer)

anto.danny

Registered User.
Local time
Today, 03:08
Joined
Nov 8, 2015
Messages
35
Greetings

I'm using the below code to export m table to excel and then open it.

Code:
Dim curPath As String

    curPath = CurrentProject.Path & "\MaintenanceData- " & _
        Format(Date, "mm-dd-yyyy") & ".xlsx"
    DoCmd.TransferSpreadsheet acExport, 10, "Installed Runway Equipment Data", curPath, -1
    Application.FollowHyperlink curPath


Now the excel file gets saved where the access file is located in, but I want it to save in a custom location say for eg: On the Desktop.

Also I want only four of the columns in the table to be exported to excel.

Thanks in advance
 

GinaWhipp

AWF VIP
Local time
Today, 06:08
Joined
Jun 21, 2011
Messages
5,901
Well, let's start with the real part...

Instead of exporting your Table create a query and export that. Do that and then we'll move on to the path part.
 

anto.danny

Registered User.
Local time
Today, 03:08
Joined
Nov 8, 2015
Messages
35
Well, let's start with the real part...

Instead of exporting your Table create a query and export that. Do that and then we'll move on to the path part.

Its done, quick question will this take space on my db?
 

GinaWhipp

AWF VIP
Local time
Today, 06:08
Joined
Jun 21, 2011
Messages
5,901
Take space? Not enough to count for anything because it's a query.

Next step, instead of using...

Code:
curPath = CurrentProject.Path & "\MaintenanceData- " & _
Format(Date, "mm-dd-yyyy") & ".xlsx"
You would use...

Code:
 curPath = "Enter your path here\MaintenanceData- " & _
        Format(Date, "mm-dd-yyyy") & ".xlsx"
 

anto.danny

Registered User.
Local time
Today, 03:08
Joined
Nov 8, 2015
Messages
35
Take space? Not enough to count for anything because it's a query.

Next step, instead of using...

Code:
curPath = CurrentProject.Path & "\MaintenanceData- " & _
Format(Date, "mm-dd-yyyy") & ".xlsx"
You would use...

Code:
 curPath = "Enter your path here\MaintenanceData- " & _
        Format(Date, "mm-dd-yyyy") & ".xlsx"

I tired this before but it gives an error "Run time error '3051'"
 

anto.danny

Registered User.
Local time
Today, 03:08
Joined
Nov 8, 2015
Messages
35
Take space? Not enough to count for anything because it's a query.

Next step, instead of using...

Code:
curPath = CurrentProject.Path & "\MaintenanceData- " & _
Format(Date, "mm-dd-yyyy") & ".xlsx"
You would use...

Code:
 curPath = "Enter your path here\MaintenanceData- " & _
        Format(Date, "mm-dd-yyyy") & ".xlsx"

I tired this before but it gives an error "Run time error '3051'"

Code:
Dim curPath As String
curPath = "c:\MaintenanceData- " & _
Format(Date, "mm-dd-yyyy") & ".xlsx"
 

anto.danny

Registered User.
Local time
Today, 03:08
Joined
Nov 8, 2015
Messages
35
on the desktop, I'll be using this db on multiple computers..how to type in directory so that it can save in the desktop regardless of the user.
 
Last edited:

GinaWhipp

AWF VIP
Local time
Today, 06:08
Joined
Jun 21, 2011
Messages
5,901
Okay then you need to enter the full path to your Desktop and then the file name, i.e.

Code:
C:\Users\YourComputerName\Desktop\

Replace YourComputerName with the name of YOUR computer.
 

Grumm

Registered User.
Local time
Today, 11:08
Joined
Oct 9, 2015
Messages
395
You can do it like this :
Code:
Dim DTPath As String 
Dim curPath As String

DTPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") 
curPath = DTPath & "\MaintenanceData- " & _
        Format(Date, "mm-dd-yyyy") & ".xlsx"
    DoCmd.TransferSpreadsheet acExport, 10, "Installed Runway Equipment Data", curPath, -1
    Application.FollowHyperlink curPath

That DTPath will return the desktop path of the user. Without having to search for what user is logged in. Hope that helps.
 

Users who are viewing this thread

Top Bottom