472,981 Members | 1,564 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 472,981 developers and data experts.

How to Export a Linked Table to Excel

245 128KB
This is something that was needed that I will post on here in case others have use for it.
Code will create a new folder with Today's date and file name, then export the table to excel. Then it will open and format the excel file after it is exported by freezing the top row, and will autofit the column width.
Probably not the best written but works for me.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  3. Public Function exportToXl()
  4. Dim sFolderName As String, sFolder As String
  5. Dim sFolderPath As String
  6. Dim dbTable As String
  7. Dim xlWorksheetPath As String
  9. 'Main Folder
  10. sFolder = "C:\Users\asdf\Documents\Backups\"
  12. 'Folder Name
  13. sFolderName = Format(Now, "mm-dd-yyyy")
  15. 'Folder Path
  16. sFolderPath = "C:\Users\asdf\Documents\Backups\" & sFolderName
  18. 'Create FSO Object
  19. Set oFSO = CreateObject("Scripting.FileSystemObject")
  21. 'Check Specified Folder exists or not
  22.     If oFSO.FolderExists(sFolderPath) Then
  23.         'If folder is available with today's date
  24.         MsgBox "Folder already exists  with today's date.", vbInformation, "VBAF1"
  25.         Exit Function
  26.     Else
  27.         'Create Folder
  28.         MkDir sFolderPath
  29.     End If
  32. xlWorksheetPath = sFolderPath & "\" & "Backup.xlsx"
  34. dbTable = "tblRecords"
  35. DoCmd.TransferSpreadsheet transfertype:=acExport, spreadsheettype:=acSpreadsheetTypeExcel12Xml, tablename:=dbTable, FileName:=xlWorksheetPath, hasfieldnames:=True
  37. ErrorHandlerExit:
  39. ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  41. Dim xl As Excel.Application
  42. Dim wb As Excel.Workbook
  43. Dim ws As Excel.Worksheet
  44. Set xl = CreateObject("Excel.Application")
  45. Set wb = xl.Workbooks.Open(xlWorksheetPath)
  46. Set ws = wb.Sheets("Data")
  48. wb.Application.ActiveWindow.FreezePanes = False
  49. ws.Range("a2").Select
  51. wb.Application.ActiveWindow.FreezePanes = True
  53. AutofitAllUsed
  55. wb.Save
  56. wb.Close
  58. ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  59. Exit Function
  60. End Function
  62. Sub AutofitAllUsed()
  64. Dim x As Integer
  66. For x = 1 To ActiveSheet.UsedRange.Columns.Count
  68.      Columns(x).EntireColumn.AutoFit
  70. Next x
  72. End Sub
Jul 15 '22 #1
0 6466

Sign in to post your reply or Sign up for a free account.

Similar topics

by: me here | last post by:
I have a VBA subroutine that links an MS Excel spreadsheet and copies the data into a local table. This process is controlled by a form that allows users to select the spreadsheet from the file...
by: deko | last post by:
How to run action query against linked table? I have an Access 2003 mdb with an Excel 2003 Workbook as a linked table. When I attempt to run an action query against the linked table I get this...
by: Keon | last post by:
Hoi, I'm using a database with alot of records in 1 table (more than 3000). If i want to export this table to excel i only get it till record 2385. Do someone know how i can solve this...
by: bwhite | last post by:
I have a temp table with one row of data that I need to export into Excel. I created the export to create the xls file as follows ... Dim FileName FileName = !! DoCmd.SetWarnings False...
by: Hans [DiaGraphIT] | last post by:
Hi! I want to export a dataset to an excel file. I found following code on the net... ( http://www.codeproject.com/csharp/Export.asp ) Excel.ApplicationClass excel = new ApplicationClass();...
by: Ed Chiu | last post by:
Hi, I copied the following codes from Internet to export datagrid to Excel, it works with datagrid not supporting sorting or paging. Response.ContentType = "application/vnd.ms-excel"...
by: christianlott1 | last post by:
I've linked an excel worksheet as an access table. The values appear but it won't allow me to change any of the values. To test I've provided a fresh blank workbook and same problem. I've done...
by: JFKJr | last post by:
Hello everyone! I am trying to export Access table data into Excel file in such a way that the table field names should be in the first line of each column in excel file followed by field data, and...
by: Bill Sublette | last post by:
Good Morning All. I know this is probably avery simple thing to do, but I'm not too familiar with Access. Some of you have probably read other posts I have created trying to get Excel to do...
by: Daryl Austin | last post by:
I successfully linked an Excel file into my Access database and using an Update query pull in information to my Quote log table. That works fine. Now I am trying to go the other direction, and use...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.