Help! My macro won't automatically start when I open Excel.

[john928]

Hi,

I am working one an Excel worksheet so that I can make it do things without me touching it once it is opened. The only thing is that the marco doesn't seem to want to automatically start when Excel is opened.

Here is my VB code.

__________________________________________-

Private Sub ThisWorkbook_Open()

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\phpdev\www\website\database\shareasale2.csv", Destination:=Range( _
"A1"))
.Name = "shareasale2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:C").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=3
Columns("E:E").Select
ActiveWindow.SmallScroll ToRight:=1
Selection.Delete Shift:=xlToLeft
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
Columns("H:H").Select
Selection.Cut
ActiveWindow.SmallScroll ToRight:=-2
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=1
Columns("I:S").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=-1
Range("I2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]&"" ""&RC[-1]"
Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I47684"), Type:=xlFillDefault
Range("I2:I47684").Select
ActiveWindow.ScrollRow = 1
ActiveWindow.SmallScroll ToRight:=1
Columns("I:I").Select
Selection.Copy
Columns("M:M").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("G:L").Select
Range("L1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=-1
Columns("G:G").Select
Selection.Cut
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=-4
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
ChDir "C:\phpdev\www\website\database"
ActiveWorkbook.SaveAs Filename:= _
"C:\phpdev\www\website\database\shareasale7.txt", FileFormat:=xlText, _
CreateBackup:=False
Application.DisplayAlerts = False
ThisWorkbook.Close
Application.DisplayAlerts = True
Application.Close
End Sub
_____________________________________

Could someone please take a look at it and see what could be causing the problem.

Thanks!

[dominicb]

Good evening John

Two things could be wrong with this:

First, the first line should be

Private Sub Workbook_Open()

I know it's only a tiny four letter word but the "This" before workbook will make a huge diference.

If your macro still doesn't auto run, where it's stored within the VBE environment will also make a difference. If it is sitting in the "modules" folder of the project window, then move the code in its entirity into the "This Workbook" project.

Hope this gets it going OK...

DominicB

[john928]

Thanks for that dominicb.

It now works successfully.

The cause was the "This" within the ThisWorkbook_Open() part.

Thanks again!