Here’s the code you can implement to assign a back-up procedure to a button. You will need to create a button and assign this macro to it, but after that, all you have to do is click the button and a back up copy will be made with the back-up date appended to the suffix of the file. This is a great little tool for those of us who forget to back things up regularly. Access databases can
become corrupt seemingly out of the blue, so make this piece of code your friend. Dim Source As String Source = CurrentDb.name 'This is the only thing to change - add the path of where you want the file to save here ' create the backup 'Opens the folder of the file you just created End Sub How to use VBA to export to Excel workbooks data from Access query datasheets and to format the worksheets once the data have arrived. Last updated on 2020-10-27 by David Wallis. PrefaceFor many of the databases I develop I include an export-to-Excel capability. This is for clients who want the means of dumping data so that they can do their own thing without risk to the primary data. Quite often an export in itself is all that’s required, instead of a carefully created Access report included in the database. Also, on occasions, the export serves as a useful check that the assembled records contain exactly what the client needs, prior to creation of a full-blown Access report. This article describes a VBA procedure for dumping data from Access into Excel, exploiting the DoCmd.TransferSpreadsheet method to take you beyond the explanation of the workings of this method that you read in many websites. Initial ConsiderationsCompounded from client requirements over the years, I've identified these are the main features of an export-to-Excel capability:
All DMW databases supplied to clients are split — FE (Front End) and BE (Back End). FEs contain queries, forms, reports, macros and modules, and, as appropriate, a table or two, as I'll explain below. BEs contain tables only. From the viewpoint of a developer, these are my considerations:
Were users to be allowed to tinker, it would be an impractical and time-consuming task for me providing upgrades and on-gong support to the client. The client would not be happy with the bill. VBA TransferSpreadsheet MethodThe procedure dmwExport uses the bare bones of the TransferSpreadsheet method to export the contents of a table, or of a query datasheet, as a named Excel file to a named folder: Sub dmwExport(query$, path$) DoCmd.TransferSpreadsheet _ TransferType:=acExport, _ SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _ TableName:=query$ ,_ FileName:=path$, _ HasFieldNames:=True End Sub dmwExport("qsResults", "S:\Reports\Results.XLSX"), for example, exports the contents of the query qsResults to the folder S:\Reports\ as an Excel file named Results.XLSX. Opening the Exported WorkbookdmwExport creates the workbook and saves it, but it doesn't display the completed workbook. Experience suggests that most users want to see the results of the export as soon as it's complete. Sub dmwExport(query$, path$) Dim xlApp As Object, wkbk As Object DoCmd.TransferSpreadsheet _ TransferType:=acExport, _ SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _ TableName:=query$, _ FileName:=path$, _ HasFieldNames:=True Set xlApp = CreateObject("Excel.Application") With xlApp .Visible = True Set wkbk = .Workbooks.Open(path$) End With End Sub Error HandlingAt this point we will include error handling and make sure that the procedure releases any connection with Excel once it has presented the workbook: Sub dmwExport(query$, path$) On Error Goto errHandler Dim xlApp As Object, wkbk As Object Dim msg$ DoCmd.TransferSpreadsheet _ TransferType:=acExport, _ SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _ TableName:=query$, _ FileName:=path$, _ HasFieldNames:=True Set xlApp = CreateObject("Excel.Application") With xlApp .Visible = True Set wkbk = .Workbooks.Open(path$) End With procDone: Set wkbk = Nothing Set xlApp = Nothing Exit Sub errHandler: msg$ = Err.Description MsgBox msg$, vbExclamation, "Unanticipated Error" Resume procDone End Sub Path and Workbook NamingYou need to take care when supplying values to the path$ argument of dmwExport(query$, path$). Consider these values: "S:\Reports\Results.XLSX" This works satisfactorily — the Workbook named Results.XLSX is directed to the S:\Reports\ folder. "S:\Reports\Results" This works satisfactorily too — the procedure attaches the .XLSX extension to the workbook's name so that Results.XLSX is directed to the S:\Reports\ folder. "S:\Reports\Results\" Here the problem arises that the final \ causes the procedure to treat S:\Reports\Results\ as a folder, without specifying any workbook at all, with resulting error conditions, e.g. — If there’s call for it (let me know), I shall add some code to contend with the issue. The full-blown export program described below incudes such code. Providing for Folder ChangesIn my experience, clients like to be able to determine for themselves to which folder the export process directs the workbooks it creates. Also, they want to be able to change that folder without needing to get me to tweak any code. This is a particular requirement when a client’s IT want the freedom to change locations for files on a network and re-map drives. Over the years, I’ve tried a number of ways of providing for this, currently settling with a method that uses an addition to the two main FE and BE files. This third file I name KEY.ini, which is a simple text file, the content of which is this: Important '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ This file to be placed in same folder as USER file Edit ExportPath to correspond to your folder structure '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DEFAULT] ExportPath = "S:\Reports\" Now we need to accommodate KEY.ini into the export process. The process must pick out the export path from KEY.ini. In tune with good practice we are going to structure our programming by separating our code into a number of sub-routines. Each of these will perform a distinct operation, one of which will be the picking from KEY.ini. From here on in dmwExport will become one of the sub-routines the running sequences of which will be determined by an overall controlling procedure. The Controlling ProcedureThis is the sequence of sub-routines that the controlling procedure, or program, named dmwExportToXL, will call: dmwGetPath This VBA-function sub-routine will look for KEY.ini and get the export path from it. If dmwGetPath cannot locate KEY.ini, or is unable the find the information about the export path, then it will return an error message to dmwExportToXL. dmwCheckPath This VBA-function sub-routine will seek to confirm the existence of the folder to which dumps are to be directed. If dmwCheckPath cannot locate that folder it will it report is as missing to dmwExportToXL. dmwExport This will complete the export program passing data from Access and into Excel, the opening Excel to display the data, and the formatting of the worksheet. This is the skeleton of the dmwExportToXL program: Sub dmwExportToXL() Dim msg$, bln As Boolean msg$ = dmwGetPathFromKEY() bln = dmwCheckPath() Call dmwExport() End Function Get Path from KEY File Sub-RoutineThe job of the sub-routine dmwGetPathFromKEY is to retrieve the path of the back-end DATA file from KEY.ini: Function dmwGetPathFromKEY(pathINI$, element$) As String On Error GoTo errHandler Dim i&, lenElement& Dim fstChar34%, lstChar34% Dim lineINI$, path$ If Len(Dir(pathINI$)) > 0 And Len(element$) > 0 Then lenElement& = Len(element$) i& = FreeFile() Open pathINI$ For Input As #i& Do While Not EOF(i&) Line Input #i&, lineINI$ If Left(lineINI$, lenElement&) = element$ Then path$ = Mid(lineINI$, lenElement& + 1) Exit Do End If Loop Close #i& fstChar34% = InStr(path$, Chr(34)) + 1 lstChar34% = InStrRev(path$, Chr(34)) path$ = Mid(path$, fstChar34%, lstChar34% - fstChar34%) Else path$ = "Error" End If procDone: dmwGetPathFromKEY = path$ Exit Function errHandler: path$ = "Error" Resume procDone End Function If it's unable to return the whereabouts of DATA, then dmwGetPathFromKEY warnings from which dmwStartUp composes messages to the user. Check Path Sub-RoutineThe job of the second sub-routine, dmwCheckPath, in the export program is to confirm that the folder specified in KEY.ini actually exists: Function dmwCheckPath(path$) As String On Error GoTo errHandler Dim msg$ If Dir(path$, vbDirectory) = "." Then msg$ = vbNullString Else msg$ = _ "No folder matches entry in KEY file" End If procDone: dmwCheckPath = msg$ Exit Function errHandler: msg$ = Err.Description Resume procDone End Function dmwCheckPath returns a null string if the folder is in place, or a message if that folder is missing or that the program cannot find it at the anticipated location. Export to Excel Sub-RoutineThe third and final subroutine in the program is dmwExport. But unlike the version at the top of this page, this version of dmwExport contains lines of code aimed at smartening up the contents of the worksheet created by the export. The smartening up that my clients most like to see are these:
Hence a revised dmwExport, now fashioned as a function procedure: Function dmwExport( _ query$, path$, _ fileName$, wksName$, _ colsCurrency$, colsDate$ _ ) As String On Error GoTo errHandler Dim xlApp As Object, wkbk As Object, wks As Object Dim file$ Dim formatCur$, formatDate$, intColor& Dim arrayCols() As String, col$, n%, i%, w! Dim cell As Range Dim msg$ ' Worksheet formats formatCur$ = "£#,##0.00" formatDate$ = "yyyy-mm-dd" intColor& = RGB(100, 200, 200) ' Create workbook file$ = path$ & fileName$ DoCmd.TransferSpreadsheet _ TransferType:=acExport, _ SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _ TableName:=query$, _ FileName:=file$, _ HasFieldNames:=True ' Open workbook Set xlApp = CreateObject("Excel.Application") With xlApp .Visible = True Set wkbk = .Workbooks.Open(file$) End With ' Format worksheet Set wks = wkbk.worksheets(1) With wks .Name = wksName$ ' Currency columns arrayCols = Split(colsCurrency$, ",") For i = LBound(arrayCols) To UBound(arrayCols) With .Columns(arrayCols(i)) .NumberFormat = formatCur$ End With Next i ' Date columns arrayCols = Split(colsDate$, ",") For i = LBound(arrayCols) To UBound(arrayCols) With .Columns(arrayCols(i)) .NumberFormat = formatDate$ End With Next i ' Filters With .Range("A1") .Select .autofilter End With ' Column width adjustments With .Cells .Select .EntireColumn.AutoFit End With n% = .Cells(1, 1).End(xlToRight).Column For i% = 1 To n% With .Cells(1, i%) w! = .EntireColumn.ColumnWidth .EntireColumn.ColumnWidth = w! + 4 .HorizontalAlignment = xlCenter .Interior.Color = intColor& .Font.Bold = True End With Next i% End With With xlApp.ActiveWindow .SplitColumn = 0 .SplitRow = 1 .FreezePanes = True End With msg$ = vbNullString procDone: Set wks = Nothing Set wkbk = Nothing Set xlApp = Nothing dmwExport = msg$ Exit Function errHandler: msg$ = _ Err.Number & ": " & Err.Description Resume procDone End Function Export to Excel Program ProcedureThis is the controlling procedure that pulls together the sub-procedures to perform the export to Excel: Function dmwExportToXL(query$, _ fileName$, wksName$, _ colsCurrency$, colsDate$) On Error GoTo errHandler Dim bln As Boolean Dim path$ Dim msg$ path$ = Left(CurrentProject.FullName, _ InStrRev(CurrentProject.FullName, "\")) path$ = path$ & "KEY.ini" path$ = dmwGetPathFromKEY(path$, "ExportPath") Select Case path$ Case "Error" msg$ = "Unanticipated error locating KEY" bln = False Case Else bln = True End Select If bln Then msg$ = dmwCheckPath(path$) If msg$ = vbNullString Then msg$ = dmwExport( _ query$, path$, _ fileName$, wksName$, _ colsCurrency$, colsDate$) Else msg$ = _ "Folder for exports cannot be located. " & msg$ End If procDone: If msg$ <> vbNullString Then MsgBox msg$, vbExclamation, "Excel Export" End If Exit Function errHandler: msg$ = Err.Number & ": " & Err.Description Resume procDone End Function This is how you might provide values for the arguments of dmwExportToXL: query$ as “qsExportSalesByMonth” — your database query fileName$ as “Export Sales” — the workbook file's name. wksName$ as “Feb 2020” — the worksheet’s name colsCurrency$ as “F:F” — Column F currency format colsDate$ as “C:C,D:D” — Columns C and D date format. Each argument value must be enclosed in the quotation marks as shown above. Check Your ReferencesThanks to Marek, who on 2020-10-14 commented wisely on an ommission from my article at the time: “Just in case this is of any help. I'm looking at your Export to Excel procedures on [this web page]. “I'm not sure if it would be helpful to add the need to add the reference to the Microsoft Excel Object Library as my version of Access was missing this. “Kind regards and thank you for sharing your code.” In your code, Access is making calls to Excel. For it to work you need to tell Access that you’re using Excel. This is how you tell Access:
The fifth action checks whether or not Access has got the hang of things. Download and DonationDownload Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content. To make a contribution by PayPal in GBP (£ sterling) — To make a contribution by PayPal in USD ($ US) — Thanks, in anticipation. Front End Table Linking FailuresOn a couple of occasions getting a new database up-and-running, users reported this error and the database failing to open: The cause of the error was lack of permissions assigned to all the users. To avoid the error, make sure that each user is given full admission to the database BE, and to the folder and drives in which it is located. How do I export data from Excel to Access VBA?Export data from Excel to Access (ADO) using VBA in Microsoft.... Open Excel.. Press ALT + F11.. VBA Editor will OPEN.. Click anywhere in the Project Window.. Click on Insert.. Click on Module.. In the Code Window, Copy and Paste the below mentioned Code. Sub ADOFromExcelToAccess() ... . Once this is pasted, go to the Excel file.. How do I save an Excel spreadsheet as an Access database?Answer:. Open the Access database.. If you receive a security warning, click the Enable Content button.. On the Office ribbon, select the External Data tab and click Excel.. The "Get External Data - Excel Spreadsheet" wizard appears. ... . Select the worksheet to import.. How do you connect Excel to Access database using Excel VBA?Method to query access database from excel Visual Basic for Applications VBA. 'specify path to db.. AccessFile = dbPath 'string variable with file location.. On Error Resume Next.. 'Create the ADODB connection object.. Set con = CreateObject("ADODB.connection"). 'Check if the object was created.. If Err.. Can Access pull data from Excel?You can bring the data from an Excel workbook into Access databases in many ways. You can copy data from an open worksheet and paste it into an Access datasheet, import a worksheet into a new or existing table, or link to a worksheet from an Access database.
|