asked on

VBA Export to Excel with Autofit

I need to modify this code to Autofit my Excel export from Access 2013.

Option Explicit

Declare Function ShellExecute Lib "shell32.dll" Alias _
"ShellExecuteA" (ByVal Hwnd As Long, ByVal lpOperation _
As String, ByVal lpFile As String, ByVal lpParameters _
As String, ByVal lpDirectory As String, ByVal nShowCmd _
As Long) As Long

Global Const SW_SHOWNORMAL = 1
Function ExportReports()

Dim exportFile As String
exportFile = "\\path\filename.xlsx"
If Len(Dir$(exportFile)) > 0 Then
Kill exportFile
End If

DoCmd.TransferSpreadsheet acExport, , "MyQry-1", exportFile, True, "New-Tab-Name"
DoCmd.TransferSpreadsheet acExport, , "MyQry-2", exportFile, True, "New-Tab-Name"
DoCmd.TransferSpreadsheet acExport, , "MyQry-3", exportFile, True, "New-Tab-Name"
DoCmd.TransferSpreadsheet acExport, , "MyQry-4", exportFile, True, "New-Tab-Name"
DoCmd.TransferSpreadsheet acExport, , "MyQry-5", exportFile, True, "New-Tab-Name"

MsgBox "Reports File Updated!"
ShellExecute Application.hWndAccessApp, "Open", exportFile, "", "C:\", SW_SHOWNORMAL
End Function

Nick67

Modify or replace?
CopyFromRecordset to an Newly created workbook is how I do that stuff.
Not TransferSpreadsheet and no API

Chris Miller

ASKER

This code deletes the file if its there.

Dim exportFile As String
exportFile = "\\path\filename.xlsx"
If Len(Dir$(exportFile)) > 0 Then
Kill exportFile
End If

Then I export a new file.

At this point, need to modify, if that the best thing to do.

Rey Obrero (Capricorn1)

what do you mean by autofit?

Chris Miller

ASKER

autofit the columns

Rey Obrero (Capricorn1)

you have to do that using VBA codes after the export.

dim xlObj as object
set xlObj=createobject("excel.application")
      xlObj.workbooks.open "\\path\filename.xlsx"
      with xlObj
            .worksheets("name of sheet").select
            .Columns("B:C").Select
            .Selection.Columns.AutoFit

            .activeworkbook.save
      end with

      xlObj.quit
      set xlObj=nothing

Nick67

I know what you mean.
Have a look here
https://www.experts-exchange.com/questions/28571045/MS-Access-Macro-Not-Exporting-to-Excel.html

You would leave this bit intact
Dim exportFile As String
exportFile = "\\path\filename.xlsx"
If Len(Dir$(exportFile)) > 0 Then
Kill exportFile
End If

The rest would be reworked along the lines of the link posted.
Read it through and let me know what I can do to help you knock yours into shape.

Once you've got an Excel automation on the go, autofitting is pretty straight forward

Format the columns for height and width
'Format the header row as bold and autofit the columns
With oSheet.Range("a1").Resize(1, iNumCols)
.Font.Bold = True
.EntireColumn.AutoFit
End With

SOLUTION

Helen Feddema

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chris Miller

ASKER

I dont know how to apply the AutoFit

Rey Obrero (Capricorn1)

dim xlObj as object
set xlObj=createobject("excel.application")
xlObj.workbooks.open "\\path\filename.xlsx"
with xlObj

' select the name of the sheet
.worksheets("name of sheet").select

' select the columns for autofit the next line selects columns B & C
.Columns("B:C").Select
.Selection.Columns.AutoFit

.activeworkbook.save
end with

xlObj.quit
set xlObj=nothing

<I dont know how to apply the AutoFit > Explain.... what are the ccolumns you want to autofit?

.

Chris Miller

ASKER

oh, ok. I didnt know I needed to list the columns, I thought it would just autofit all of them.

For this example A-I

ASKER CERTIFIED SOLUTION

Nick67

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial