help with export excel in vba!

[anto.danny]

Greetings

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

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]

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]

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]

Take space? Not enough to count for anything because it's a query.

Next step, instead of using...

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

You would use...

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

 

[anto.danny]

Take space? Not enough to count for anything because it's a query.

Next step, instead of using...

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

You would use...

 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]

Take space? Not enough to count for anything because it's a query.

Next step, instead of using...

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

You would use...

 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'"

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

 

[GinaWhipp]

Where are you trying to save the file?

 

[anto.danny]

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.

 

[GinaWhipp]

Okay then you need to enter the full path to your Desktop and then the file name, i.e.

C:\Users\YourComputerName\Desktop\

Replace YourComputerName with the name of YOUR computer.

 

[Grumm]

You can do it like this :

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.

 

[anto.danny]

You Sir are the "ONE"