Export to excel (1 Viewer)

Tor_Fey

Registered User.
Local time
Today, 08:50
Joined
Feb 8, 2013
Messages
121
Morning All;

I have the following export to excel code on a button in my form; which is working well:

Code:
'Check for and deletes the current All_Live_For_Sifting_Cred.xlsx file located: E:\All_Temporary_Data
   Dim stAppName As String
    stAppName = "E:\All_Temporary_Data\Delete(All_Live_For_Sifting_Cred.xlsx).bat"
        Call Shell(stAppName, 1)


'Exports the form: frm_all_live_for_sifting_cred to an Excel file (.xlsx format) called: All_Live_For_Sifting_Cred.xlsx, located in the: E:\All_Temporary_Data area
DoCmd.OutputTo ObjectType:=acOutputForm, ObjectName:="frm_all_live_for_sifting_cred", OutputFormat:=acFormatXLSX, Outputfile:="E:\All_Temporary_Data\All_Live_For_Sifting_Cred.xlsx"

'Opens the All_Live_For_Sifting_Cred.xlsx Spreadsheet from E:\All_Temporary_Data
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Set xlApp = New Excel.Application
    With xlApp
        .Visible = True
    Set xlWB = .Workbooks.Open("E:\All_Temporary_Data\All_Live_For_Sifting_Cred.xlsx", , False)
    End With

End Sub

What i would like o do is; have this code delete the first column from the excel file: All_Live_For_Sifting_Cred.xlsx before opening it. Is this possible and how do i modify my code to do this?

Kind Regards
Tor Fey
 

Ranman256

Well-known member
Local time
Today, 04:50
Joined
Apr 9, 2015
Messages
4,339
Code:
   Set xlWB = .Workbooks.Open("E:\All_Temporary_Data\All_Live_For_Sifting_Cred.xlsx", , False)

.columns("A:A").select
.Selection.Delete Shift:=xlToLeft
 

Tor_Fey

Registered User.
Local time
Today, 08:50
Joined
Feb 8, 2013
Messages
121
Thanks so much Ranman256;

This code now works as i expect. But i have ran in to a slight issue.

My Excel object library is 15.0; and everything works great, but some people are still only working with office 2010 and require the 14.0 object library.
When running the code it either gives a dll error or crashes the database.

Is there a way to make the excel output none object library specific?

Kind Regards
Tor Fey

Code:
   Set xlWB = .Workbooks.Open("E:\All_Temporary_Data\All_Live_For_Sifting_Cred.xlsx", , False)

.columns("A:A").select
.Selection.Delete Shift:=xlToLeft
 

isladogs

MVP / VIP
Local time
Today, 08:50
Joined
Jan 14, 2017
Messages
18,186
Use late binding. Then the Excel reference can be removed.
 

Tor_Fey

Registered User.
Local time
Today, 08:50
Joined
Feb 8, 2013
Messages
121
Thanks Ridders;

is this just simply adding the following:

Code:
Set Connection = GetObject(, "ADODB.Connection")
Dim xlApp As Excel.Application

New to late bindings.

Regards
Tor Fey

Use late binding. Then the Excel reference can be removed.
 

Tor_Fey

Registered User.
Local time
Today, 08:50
Joined
Feb 8, 2013
Messages
121
Hi Ridders;

Thanks for your help; i have found the solution; thanks for pointing out late bindings, very useful.

I went with this code which seems to work now:
Code:
Dim xlApp As Object
Dim thisWb As Object 'not Workbook
Set xlApp = CreateObject("Excel.Application")
    With xlApp
        .Visible = True
    Set xlWB = .Workbooks.Open("E:\All_Temporary_Data\All_Live_For_Sifting_Cred.xlsx", , False)
    .Columns("A:A").Select
    .Selection.Delete Shift:=xlToLeft

Kind Regards
Tor Fey

Thanks Ridders;

is this just simply adding the following:

Code:
Set Connection = GetObject(, "ADODB.Connection")
Dim xlApp As Excel.Application

New to late bindings.

Regards
Tor Fey
 

isladogs

MVP / VIP
Local time
Today, 08:50
Joined
Jan 14, 2017
Messages
18,186
Hi

Sorry I've been offline most of the day
You seem to have got the idea

Here's a summary of the differences

Early Binding

Code:
' Set reference to 'Microsoft Excel Object Library' in the VBA|References dialog 

' Declare the object as an early-bound object
  Dim oExcel As Excel.Application

  Set oExcel = CreateObject("Excel.Application")

Advantage: intellisense available
Disadvantage: version dependant

Late Binding
Code:
' No reference to a type library is needed to use late binding.
' The method can be dynamically located and invoked at run-time.

' Declare the object as a late-bound object
  Dim oExcel As Object

  Set oExcel = CreateObject("Excel.Application")

Advantage: NOT version dependant
Disadvantage: intellisense NOT available

Many developers will write code using early binding to get the use of intellisense. Then change to late binding and remove the reference as no longer required

Many articles about this online. For example: https://support.microsoft.com/en-us/help/245115/using-early-binding-and-late-binding-in-automation
 

Users who are viewing this thread

Top Bottom