Access forms to Excel

[Stevemil]

Hey everyone.
I have been searching for an answer and no luck so I figured I would join and see if you can help.
I have an unbound form that has a lot of information on it that I load with VB. I need to get this data into Excel AND preserve the format of the form in Excel. I know there is no magic “export to excel” button so I am thinking about exporting it using VBA.

Can I use some automation to export the data to an Excel template? Has anyone had any experience with this?

Any help in the right direction would be great.

Thanks
Steve.

 

[Kempes]

Either create a macro and choose "output to", or code it using

Docmd.OutputTo

This should do the job

 

[DCrake]

Simple Software Solutions

There are various ways of "sending" access data to Excel. Microsoft always does this using new workbooks, however, you want to populate an existing worksheet in an existing workbook. This is also possible.

Here is an example of this task. Remember to change the varibles to suit your own needs:

Function PTLSubmission(SubmissionDate As Date)
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object

Dim TemplatePath As String
Dim TemplateName As String

Dim SaveInFolderPath As String
Dim NewFileName As String

Dim WkNo As String
Dim WkCommencingDate As Date

Dim Under18 As Integer
Dim Over18 As Integer


Dim bOverwrite As Boolean
    TemplateName = "PTL Template.xls"
    TemplatePath = SysProgPath & "\Templates\"
    SaveInFolderPath = SysProgPath & "\Submissions\"
    
If PathExists(TemplatePath) = False Then
    MsgBox "Cannot find the PTL template. Please contact technical support", vbExclamation + vbOKOnly, "Missing Path or filename"
    Exit Function
Else
    'Does the teplate file exist
    If Dir(TemplatePath & TemplateName) = "" Then
        MsgBox "The required template file cannot be found. Please contact your administrator or software support", vbExclamation + vbOKOnly, "Missing Template File"
        Exit Function
    End If
    
End If

'This will only happen the first time the procedure is called
If PathExists(SaveInFolderPath) = False Then
    CreateNewFolder (SysProgPath & "\Submissions")
End If

'Create the name of the intended submission file
'Format: PTL-40-20070701.xls

    WkCommencingDate = SubmissionDate ' GetWeekCommeningDate()
    WkNo = Format(WkCommencingDate, "ww", vbMonday, vbFirstJan1)
    NewFileName = "PTL-" & WkNo & "-" & Format(WkCommencingDate, "yyyymmdd") & ".xls"
    
'Check to see if this submission has already been created
If Dir(SaveInFolderPath & NewFileName) <> "" Then
    If MsgBox("A submission for this period has already been generated." & vbCrLf & vbCrLf & "Do you want replace the existing file?", vbQuestion + vbYesNo + vbDefaultButton2, "Duplicate file name") = vbYes Then
        bOverwrite = True
    End If
    
    'delete the previous version if it already exists.
    If bOverwrite = True Then
        Kill SaveInFolderPath & NewFileName
    Else
        Exit Function
    End If
End If

    'Copy the template file to the submissions folder
   ' Call CopyFiles(TemplatePath & TemplateName, SaveInFolderPath & NewFileName)
  '  DoEvents
    
    'Check to see if the above operation was successful. It may not be due to access rights.
'If Dir(SaveInFolderPath & NewFileName) = "" Then
'    MsgBox "There was a problem creating a new submission file on you machine." & vbCrLf & vbCrLf & "This may be due to your access rights and privilages," & vbCrLf & "please contact your administrator or software support.", vbCritical + vbOKOnly, "Unable to create file - Access denined"
'    Exit Function
'End If

    
'Now that all the validation has taken place we can now open the saved template file and edit then save

Under18 = GetPTLData(WkCommencingDate, "U")
Over18 = GetPTLData(WkCommencingDate, "O")


Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(TemplatePath & TemplateName)
Set xlSheet = xlBook.Worksheets(1)

xlSheet.Range("A2").Value = Replace(FinancialYear(Date), "/20", "/")
xlSheet.Range("A3").Value = Replace(FinancialYear(Date), "/20", "/")
xlSheet.Range("A4").Value = Replace(FinancialYear(Date), "/20", "/")
xlSheet.Range("A5").Value = Replace(FinancialYear(Date), "/20", "/")
xlSheet.Range("B2").Value = "W/E " & Format(WkCommencingDate, "dd/mm/yyyy")
xlSheet.Range("B3").Value = "W/E " & Format(WkCommencingDate, "dd/mm/yyyy")
xlSheet.Range("B4").Value = "W/E " & Format(WkCommencingDate, "dd/mm/yyyy")
xlSheet.Range("B5").Value = "W/E " & Format(WkCommencingDate, "dd/mm/yyyy")


'Next extract the items of data to submitted
xlSheet.Range("V3").Value = Under18
xlSheet.Range("W3").Value = Over18

'Save the results and quit
xlBook.SaveAs SaveInFolderPath & NewFileName

xlApp.Quit
MsgBox "PTL Submission created sucessfully." & vbCrLf & vbCrLf & "File name:" & NewFileName & vbCrLf & "Location:" & SaveInFolderPath, vbInformation + vbOKOnly, "STIES Submission Report"
'Destroy all instances of the objects
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing
End Function

Basically it gets the original template copies it to a new file and populates that one. This is to ensure that you always keep an original copy of your template.

CodeMaster: