Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-02-2018, 02:00 AM   #1
Tor_Fey
Newly Registered User
 
Join Date: Feb 2013
Location: Isle of Man
Posts: 104
Thanks: 67
Thanked 9 Times in 8 Posts
Tor_Fey is on a distinguished road
Question Export to excel

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

Tor_Fey is offline   Reply With Quote
Old 02-02-2018, 02:23 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,052
Thanks: 0
Thanked 668 Times in 653 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Export to excel

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

.columns("A:A").select
.Selection.Delete Shift:=xlToLeft
Ranman256 is offline   Reply With Quote
The Following User Says Thank You to Ranman256 For This Useful Post:
Tor_Fey (02-02-2018)
Old 02-02-2018, 03:47 AM   #3
Tor_Fey
Newly Registered User
 
Join Date: Feb 2013
Location: Isle of Man
Posts: 104
Thanks: 67
Thanked 9 Times in 8 Posts
Tor_Fey is on a distinguished road
Re: Export to excel

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

Quote:
Originally Posted by Ranman256 View Post
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 is offline   Reply With Quote
Old 02-02-2018, 03:48 AM   #4
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,128
Thanks: 83
Thanked 1,506 Times in 1,406 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: Export to excel

Use late binding. Then the Excel reference can be removed.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
Tor_Fey (02-02-2018)
Old 02-02-2018, 04:12 AM   #5
Tor_Fey
Newly Registered User
 
Join Date: Feb 2013
Location: Isle of Man
Posts: 104
Thanks: 67
Thanked 9 Times in 8 Posts
Tor_Fey is on a distinguished road
Re: Export to excel

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

Quote:
Originally Posted by ridders View Post
Use late binding. Then the Excel reference can be removed.
Tor_Fey is offline   Reply With Quote
Old 02-02-2018, 04:30 AM   #6
Tor_Fey
Newly Registered User
 
Join Date: Feb 2013
Location: Isle of Man
Posts: 104
Thanks: 67
Thanked 9 Times in 8 Posts
Tor_Fey is on a distinguished road
Re: Export to excel

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

Quote:
Originally Posted by Tor_Fey View Post
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
Tor_Fey is offline   Reply With Quote
Old 02-02-2018, 08:42 AM   #7
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,128
Thanks: 83
Thanked 1,506 Times in 1,406 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: Export to excel

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/...-in-automation


__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
Tor_Fey (02-04-2018)
Reply

Tags
excel , export access to excel , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] access export to excel thru vba but excel file Dumb on Open sumox Modules & VBA 1 06-04-2014 10:37 AM
Export to Excel using Docmd.OutputTo results in Chinese characters in Excel lkp12 Excel 1 09-27-2012 02:06 AM
Export to Excel from Access 2010 - Control Messages in Excel Jim Dudley Modules & VBA 5 03-29-2012 07:48 AM
Question Export A Form's Recordset To Excel (only export visible datasheet fields) ghudson General 15 12-08-2010 12:29 PM
Export Access Table to Excel- with the date as part of the new Excel file name WinDancer Macros 10 01-22-2008 01:10 PM




All times are GMT -8. The time now is 04:00 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World