Access and Excel automation with VBA 1

[Darci]

Hi, I am trying to export information from an access database into an excel spreadsheet. I am using the docmd.output method and it keeps overwriting my file. I need to have a way to automate the export while still keeping my controls in the excel sheet. I also need this to be as automated as possible -- trying to control the users by only allowing them to click on buttons. Any suggestions?

 

[neemi]

tell me more about what you want as I think I have done something very similar.

Do you want to create a new spreadsheet each time that is not to be overwritten?

You want the user to press a button on the form and it exports to a spreadsheet you already have or a new one?

 

[AWithers]

One way is to suffix the export filename with a date-time stamp and/or username stamp

e.g. something like this (sorry havne syntax checked it)

dim exportfilename as string

exportfilename ="Yourfilename"&format(now(),"DDMMYYHHMMSS&quot

Andy

 

[neemi]

I use two simple functions which to output data from a database to an excel spreadsheet:-

You can have a simple form with a text box where the user types in the path/filename of the spreadsheet they want exported to. The text box should be linked to a table to store the path/filename. With the form property "Cycle" set to "current Record". Data Entry set to No. The Spreadsheet does not need to exist as a new one will be created on export.

If the path/filename is of an existing spreadsheet then this spreadsheet will be overwritten

Then set up a global variable as follows:-

Public g_strFileName as String

In a new module (name it Export) paste the following code:-

Function ExportToExcel(Path As String)

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qsel_main", Path

' replace where I  have qsel_main to what table or query you are trying to export


End Function




Function ExportSpreadsheet()
    
g_strSSFileName = DLookup("[Location]", "[tbl_Location]")

'the above line is assuming you have called the table where you have stored the location of the spreadsheet as discussed above tbl_Location and the field name is Location


    ExportToExcel (g_strSSFileName)
    Beep
    MsgBox "The data from the Report has succesfully been exported to.... " & vbCrLf & vbCrLf & g_strSSFileName, vbInformation + vbOKOnly, "Exported"
            
End Function

now Create a button which your user will select to export the data to the spreadsheet and call the button cmd_Export.

On the on click event of this button paste the following code:-

Private Sub cmd_Export_Click()
    
    ExportSpreadsheet
    
End Sub

Tell me if this works out for you and if you need anymore help with the above.

Cheers,

Neemi

 

[Darci]

Thanks Neemi,

I looked at the code you wrote and I am not sure if it will do what I need I am going to try it. Here is exactly what I am looking for -- I have a user who needs to export data from a query in my database. He wants to be able to export the information into an existing spreadsheet. The data can be overwritten it is just the click button with code that needs to be preserved. He wants to use the same spreadsheet every time he exports this data. Is this possible and is this what the code you provided will do?

Thanks again

 

[Darci]

Just to clarify the last post. The user is working from a form in the database. He wants to click a button in the database and export the information to a spreadsheet in excel. The spreadsheet he is exporting to has some controls already programmed in it. The way I have it coded now overwrites the spreadsheet and of course, loses the controls. I want him to be able to use the same spreadsheet every time.

I hope I am being more clear about what I need! Thanks!

 

[neemi]

That is exactly what the code will do. You can leave the bit out where you create the form for the user to enter the path and filename of the Spreadsheet (All this does is allow you a little more versitility with your application).

You can still include it or you can hard_code the location of the spreadsheet you want to export to. Each time it is exported it will overwrite the details in the existing spreadsheet.

If you follow the steps I have written you should find it straight forward to get done.

I did exactly the same in one of my applications and it works great.

If you get stuck just let me know and I'll see what I do.

Good luck.

Let me know how you get on.

It will literally take about two minutes to put together.

 

[neemi]

what type of controls does it use?

controls as in calculations on the spreadsheet?

 

[Darci]

Thanks so much neemi! The controls are mainly click buttons that have code behind them. I will let you know shortly how I am doing! Thanks again.

 

[Darci]

Neemi, it works! Thank you so much!

Darci

 

[neemi]

No problems...

Was happy I could help