VBA Export to Excel makes .XLSX AND .XLK backup

[GiSquared]

I am ok on my VBA, I had an old code that was great for exporting to Excel on my C: drive and formatting the excel sheet.

It has never done this before, but all of a sudden, the code is making 2 files. The desired *.xlsx and a backup*.xlk file. While the backup file doesn't accumulate when I export the file over and over...it is annoying and ticking me off.:banghead:. How do I stop this? Below is my code. TIA for all your help.

Private Sub Command7_Click()
'Kills/Deletes The old file
Dim strFile

strFile = C Drive and file \Active-Pred Comparison-2014-.xlsx"

If Dir(strFile) <> vbNullString Then
Kill (strFile)
End If
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acExport, 10, "07a_Activity-Pred_Complete", " C Drive and file \Active-Pred Comparison-2014-.xlsx"
Set oApp = CreateObject("Excel.Application")
oApp.Workbooks.Open FileName:= C Drive and file \Active-Pred Comparison-2014-.xlsx"

oApp.Visible = False

'oApp.Sheets("07a_Activity-Pred_Complete").Select
'oApp.Sheets("07a_Activity-Pred_Complete").Name = "Activity-Pred Report"
oApp.Range("A1").Select
LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

oApp.Range("A1").Select


oApp.ActiveSheet.Range("A1:H" & LastRow).Sort Key1:=ActiveSheet.Columns("E"), Order1:=xlAscending, Header:=xlYes
oApp.ActiveSheet.Range("A1:H" & LastRow).Sort Key1:=ActiveSheet.Columns("B"), Order1:=xlAscending, Header:=xlYes

'oApp.ActiveCell.Offset(1).EntireRow.Insert
oApp.Range("A1:H20000").Font.Name = "Arial"
oApp.Range("A1:H20000").Font.Size = 8
oApp.Range("A1:H1").Interior.Color = RGB(141, 180, 226)
'oApp.Range("A2:H2").Interior.Color = RGB(255, 255, 153)
oApp.Range("A1:H1").Font.Bold = True
'oApp.Range("A2:H2").AutoFilter
oApp.Range("A1").Select
oApp.Range("2:2").Select
oApp.ActiveWindow.FreezePanes = True
oApp.Columns("A:H").EntireColumn.AutoFit

oApp.Range("A1:H20000").HorizontalAlignment = xlCenter
oApp.Range("A1:H1").Borders(xlEdgeBottom).LineStyle = xlContinuous
oApp.Range("A1:H1").Borders(xlEdgeBottom).Weight = xlThin
oApp.Range("A1:H1").Borders(xlEdgeLeft).LineStyle = xlContinuous
oApp.Range("A1:H1").Borders(xlEdgeLeft).Weight = xlThin
oApp.Range("A1:H1").Borders(xlEdgeRight).LineStyle = xlContinuous
oApp.Range("A1:H1").Borders(xlEdgeRight).Weight = xlThin
oApp.Range("A1:H1").Borders(xlEdgeTop).LineStyle = xlContinuous
oApp.Range("A1:H1").Borders(xlEdgeTop).Weight = xlThin
oApp.Range("A1:H1").Borders(xlInsideVertical).LineStyle = xlContinuous
oApp.Range("A1:H1").Borders(xlInsideVertical).Weight = xlThin
oApp.Range("A1:H1").Borders(xlInsideHorizontal).LineStyle = xlContinuous
oApp.Range("A1:H1").Borders(xlInsideHorizontal).Weight = xlThin

oApp.Range("A1").Select
oApp.DisplayAlerts = False

oApp.ActiveWorkbook.Save
MsgBox "Export Complete"

oApp.Quit
End Sub

 

[GiSquared]

I had to remove the c:\folder\filename links until I have 10 posts... so know that I have a proper link and file name. Sorry.

 

[JHB]

Look at the post #2 in the link.
http://www.mrexcel.com/forum/excel-...el-backup-file-visual-basic-applications.html

 

[GiSquared]

Thank you but it won't let me run the XLApp with the o.App The program keeps stopping there.

 

[JHB]

Sorry - I can't follow you, keep stopping where?

 

[GiSquared]

Ok...when I run the script with XLApp lines I get "MICROSOFT VISUAL BASIC" popup window with "RUN-TIME ERROR '424': Object Required" Then buttons "Continue"(Greyed out) "End" "Debug" "Help"

I hit Debug
The highlighted line is "XLAPP.DisplayAlerts = False"

 

[JHB]

Ofcause you've to replace the object references to your own. So in all lines where the "XLAPP" is, replace it with yours!
Example:

[COLOR=Blue][B]XLAPP[/B].[/COLOR]DisplayAlerts = False
In your code:
[B][COLOR=Red]oApp[/COLOR][/B].DisplayAlerts = False

h

 

[GiSquared]

Ok...got a tad bit further...thank you for helping... now its stopping oApp.ActiveWorkbook.SaveAs oApp.ActiveWorkbook.FullName

I am getting a new message now... Run-Time Error '91' Object variable or With block variable not set

I tried to put in the path and file name...but that didn't work either.

Set oApp = CreateObject("excel.application")
oApp.DisplayAlerts = False
oApp.ActiveWorkbook.SaveAs oApp.ActiveWorkbook.FullName, CreateBackup:=False
oApp.ActiveWorkbook.Close SaveChanges:=True
oApp.DisplayAlerts = True

 

[GiSquared]

This is how I wrote it with the path

oApp.ActiveWorkbook.SaveAs oApp.ActiveWorkbook.FullName, p<path file name is here>, CreateBackup:=False

 

[JHB]

I am getting a new message now... Run-Time Error '91' Object variable or With block variable not set

I tried to put in the path and file name...but that didn't work either.

Set oApp = CreateObject("excel.application")
oApp.DisplayAlerts = False
oApp.ActiveWorkbook.SaveAs oApp.ActiveWorkbook.FullName, CreateBackup:=False
oApp.ActiveWorkbook.Close SaveChanges:=True
oApp.DisplayAlerts = True

Yeah - because you haven't open a Workbook in the code.

 Dim oApp As Object
  Set oApp = CreateObject("excel.application")
[B][COLOR=Red] 'Change "C:\Access programmer\JDE.xls" to where your file is + your filename and extension.[/COLOR][/B]
  oApp.Workbooks.Open FileName:=[COLOR=Red][B]"C:\Access programmer\JDE.xls"[/B][/COLOR]
  oApp.Visible = True
  oApp.DisplayAlerts = False
  oApp.ActiveWorkbook.SaveAs oApp.ActiveWorkbook.FullName, CreateBackup:=False
  oApp.ActiveWorkbook.Close SaveChanges:=True
  oApp.DisplayAlerts = True

 

[GiSquared]

JHB you rock...it worked (at least on the first one...should work for the rest).

I did make one change though.
Instead of:
oApp.ActiveWorkbook.SaveAs oApp.ActiveWorkbook.FullName, CreateBackup:=False
(I got an error here)

I did this
oApp.ActiveWorkbook.SaveAs FileName:="C:\Your File Name&Path here.xlsx", CreateBackup:=False

Thank you so much for your patience and guidance.

 

[JHB]

You're welcome, good you got it.