Excel Marco Startup Problem: Run-time Error 91

Let me start by saying that I am very new to VB. About a couple of days
into attempting to learn on my own. I have the below VB code that works
fine when ran as a Marco once a .XLS spreadsheet is open. But when I
try to run it on startup in the ThisWorkbook by calling the code like
this:

Private Sub Workbook_Open()
SaveAsCSV
Sub SaveAsCSV()

I get Run-time Error 91 Object variable of With Block Not Set. Anyone
have any thoughts?


MY EXAMPLE VB MACRO CODE:
===================================================================
'This script will automatically convert and rename the current
spreadsheet to a CSV file
'in the same directory path. It will notify you if you are about to
overwrite an exsiting file
'with the same filename and allow you to select YES or NO.

Dim SavePath As String
Dim ShortFilename As String
Dim Filename As String

SavePath = ActiveWorkbook.Path & "\"
ShortFilename = Left(ActiveWorkbook.Name,
InStr(ActiveWorkbook.Name, ".") - 1) & ".csv"
Filename = SavePath & ShortFilename
ChDir SavePath

If ActiveWorkbook.Name = ShortFilename Then
MsgBox (ShortFilename & ": File Active. File Cannot Be
Overwritten")
End
End If

If Dir(Filename) <> "" Then
Style = vbYesNo
Exists = MsgBox("File Exsists: Overwrite?" & vbNewLine, Style)

If Exists = vbYes Then
Kill Filename
End If
End If

ActiveWorkbook.SaveAs Filename:=Filename, FileFormat:=xlCSV, _
CreateBackup:=True

If Dir(Filename) <> "" Then
MsgBox (Filename & ": File Created Successfully!!")
End If

End Sub
===================================================================

Try:

Private Sub Workbook_Open()
SaveAsCSV
End sub

In a standard module:

Sub SaveAsCSV()
.... Your code
End Sub

"[email protected]" wrote:

> Let me start by saying that I am very new to VB. About a couple of days
> into attempting to learn on my own. I have the below VB code that works
> fine when ran as a Marco once a .XLS spreadsheet is open. But when I
> try to run it on startup in the ThisWorkbook by calling the code like
> this:
>
> Private Sub Workbook_Open()
> SaveAsCSV
> Sub SaveAsCSV()
>
> I get Run-time Error 91 Object variable of With Block Not Set. Anyone
> have any thoughts?
>
>
> MY EXAMPLE VB MACRO CODE:
> ===================================================================
> 'This script will automatically convert and rename the current
> spreadsheet to a CSV file
> 'in the same directory path. It will notify you if you are about to
> overwrite an exsiting file
> 'with the same filename and allow you to select YES or NO.
>
> Dim SavePath As String
> Dim ShortFilename As String
> Dim Filename As String
>
> SavePath = ActiveWorkbook.Path & "\"
> ShortFilename = Left(ActiveWorkbook.Name,
> InStr(ActiveWorkbook.Name, ".") - 1) & ".csv"
> Filename = SavePath & ShortFilename
> ChDir SavePath
>
> If ActiveWorkbook.Name = ShortFilename Then
> MsgBox (ShortFilename & ": File Active. File Cannot Be
> Overwritten")
> End
> End If
>
> If Dir(Filename) <> "" Then
> Style = vbYesNo
> Exists = MsgBox("File Exsists: Overwrite?" & vbNewLine, Style)
>
> If Exists = vbYes Then
> Kill Filename
> End If
> End If
>
> ActiveWorkbook.SaveAs Filename:=Filename, FileFormat:=xlCSV, _
> CreateBackup:=True
>
> If Dir(Filename) <> "" Then
> MsgBox (Filename & ": File Created Successfully!!")
> End If
>
> End Sub
> ===================================================================
>
>

Yes I can get that to work fine. I created an icon in Excel for the
macro and it will work that way. My problem is when I try to have it
run when a spreadsheet is opened. I need it to automatically run this
marco that converts the XLS to CSV.

Toppers wrote:
> Try:
>
> Private Sub Workbook_Open()
> SaveAsCSV
> End sub
>
> In a standard module:
>
> Sub SaveAsCSV()
> ... Your code
> End Sub
>
> "[email protected]" wrote:
>
> > Let me start by saying that I am very new to VB. About a couple of days
> > into attempting to learn on my own. I have the below VB code that works
> > fine when ran as a Marco once a .XLS spreadsheet is open. But when I
> > try to run it on startup in the ThisWorkbook by calling the code like
> > this:
> >
> > Private Sub Workbook_Open()
> > SaveAsCSV
> > Sub SaveAsCSV()
> >
> > I get Run-time Error 91 Object variable of With Block Not Set. Anyone
> > have any thoughts?
> >
> >
> > MY EXAMPLE VB MACRO CODE:
> > ===================================================================
> > 'This script will automatically convert and rename the current
> > spreadsheet to a CSV file
> > 'in the same directory path. It will notify you if you are about to
> > overwrite an exsiting file
> > 'with the same filename and allow you to select YES or NO.
> >
> > Dim SavePath As String
> > Dim ShortFilename As String
> > Dim Filename As String
> >
> > SavePath = ActiveWorkbook.Path & "\"
> > ShortFilename = Left(ActiveWorkbook.Name,
> > InStr(ActiveWorkbook.Name, ".") - 1) & ".csv"
> > Filename = SavePath & ShortFilename
> > ChDir SavePath
> >
> > If ActiveWorkbook.Name = ShortFilename Then
> > MsgBox (ShortFilename & ": File Active. File Cannot Be
> > Overwritten")
> > End
> > End If
> >
> > If Dir(Filename) <> "" Then
> > Style = vbYesNo
> > Exists = MsgBox("File Exsists: Overwrite?" & vbNewLine, Style)
> >
> > If Exists = vbYes Then
> > Kill Filename
> > End If
> > End If
> >
> > ActiveWorkbook.SaveAs Filename:=Filename, FileFormat:=xlCSV, _
> > CreateBackup:=True
> >
> > If Dir(Filename) <> "" Then
> > MsgBox (Filename & ": File Created Successfully!!")
> > End If
> >
> > End Sub
> > ===================================================================
> >
> >

Hi again,
I ran your code and it executed OK i.e. calling the macro
from Workbook_Open, creating a CSV file with same name as the spreadsheet.


"[email protected]" wrote:

> Let me start by saying that I am very new to VB. About a couple of days
> into attempting to learn on my own. I have the below VB code that works
> fine when ran as a Marco once a .XLS spreadsheet is open. But when I
> try to run it on startup in the ThisWorkbook by calling the code like
> this:
>
> Private Sub Workbook_Open()
> SaveAsCSV
> Sub SaveAsCSV()
>
> I get Run-time Error 91 Object variable of With Block Not Set. Anyone
> have any thoughts?
>
>
> MY EXAMPLE VB MACRO CODE:
> ===================================================================
> 'This script will automatically convert and rename the current
> spreadsheet to a CSV file
> 'in the same directory path. It will notify you if you are about to
> overwrite an exsiting file
> 'with the same filename and allow you to select YES or NO.
>
> Dim SavePath As String
> Dim ShortFilename As String
> Dim Filename As String
>
> SavePath = ActiveWorkbook.Path & "\"
> ShortFilename = Left(ActiveWorkbook.Name,
> InStr(ActiveWorkbook.Name, ".") - 1) & ".csv"
> Filename = SavePath & ShortFilename
> ChDir SavePath
>
> If ActiveWorkbook.Name = ShortFilename Then
> MsgBox (ShortFilename & ": File Active. File Cannot Be
> Overwritten")
> End
> End If
>
> If Dir(Filename) <> "" Then
> Style = vbYesNo
> Exists = MsgBox("File Exsists: Overwrite?" & vbNewLine, Style)
>
> If Exists = vbYes Then
> Kill Filename
> End If
> End If
>
> ActiveWorkbook.SaveAs Filename:=Filename, FileFormat:=xlCSV, _
> CreateBackup:=True
>
> If Dir(Filename) <> "" Then
> MsgBox (Filename & ": File Created Successfully!!")
> End If
>
> End Sub
> ===================================================================
>
>