473,288 Members | 2,350 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,288 developers and data experts.

How to Export a Linked Table to Excel

anoble1
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
  2.  
  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
  8.  
  9. 'Main Folder
  10. sFolder = "C:\Users\asdf\Documents\Backups\"
  11.  
  12. 'Folder Name
  13. sFolderName = Format(Now, "mm-dd-yyyy")
  14.  
  15. 'Folder Path
  16. sFolderPath = "C:\Users\asdf\Documents\Backups\" & sFolderName
  17.  
  18. 'Create FSO Object
  19. Set oFSO = CreateObject("Scripting.FileSystemObject")
  20.  
  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
  30.  
  31.  
  32. xlWorksheetPath = sFolderPath & "\" & "Backup.xlsx"
  33.  
  34. dbTable = "tblRecords"
  35. DoCmd.TransferSpreadsheet transfertype:=acExport, spreadsheettype:=acSpreadsheetTypeExcel12Xml, tablename:=dbTable, FileName:=xlWorksheetPath, hasfieldnames:=True
  36.  
  37. ErrorHandlerExit:
  38.  
  39. ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  40.  
  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")
  47.  
  48. wb.Application.ActiveWindow.FreezePanes = False
  49. ws.Range("a2").Select
  50.  
  51. wb.Application.ActiveWindow.FreezePanes = True
  52.  
  53. AutofitAllUsed
  54.  
  55. wb.Save
  56. wb.Close
  57.  
  58. ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  59. Exit Function
  60. End Function
  61.  
  62. Sub AutofitAllUsed()
  63.  
  64. Dim x As Integer
  65.  
  66. For x = 1 To ActiveSheet.UsedRange.Columns.Count
  67.  
  68.      Columns(x).EntireColumn.AutoFit
  69.  
  70. Next x
  71.  
  72. End Sub
  73.  
Jul 15 '22 #1
0 6563

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

Similar topics

0
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...
5
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...
7
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...
3
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...
4
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();...
1
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"...
4
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...
0
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...
1
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...
5
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...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

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.