CODEWITHSUNDEEP
vbaexcelsave-as
Kirsteen Ng
Kirsteen Ng

Reputation: 111

VBA ActiveWorkbook.Saveas runtime error 1004

I am new to VBA and trying to make a copy from ws to wscsv and save the latter as .csv file. The following is my sub routine.

I am encountering:

run-time error 1004: Application undefined

at this line:

ActiveWorkbook.SaveAs Filename:=savedirectory, FileFormat:=xlCSV, ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges  

csvworkbook = ActiveWorkbook.Name

My Code

Sub AddNewWorkbook1(ws As Worksheet)

  ws.Activate
  MsgBox ("adding new workbook for" & ws.Name)
  Dim wscsv As Excel.Workbook
  Dim savedirectory As String
  Dim currentworkbook As String
  Dim csvworkbook As String
  currentworkbook = ws.Name

  savedirectory = '/Users/Desktop/Magnum/' & currentworkbook

  Dim lrow As Long
  lrow = Columns("A").End(xlDown).Row

  Workbooks.Add
  DisplayAlerts = False
  ActiveWorkbook.SaveAs (Filename:=savedirectory, FileFormat:=xlCSV, ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges)

  csvworkbook = ActiveWorkbook.Name

  Set wscsv = ActiveWorkbook
  MsgBox ("Entering copying")

  ws.Range(ws.Cells(2, 1), ws.Cells(lrow, 4)).Copy
  wscsv.Sheets(1).Range("A1").PasteSpecial xlPasteValues
  ws.Range(ws.Cells(2, "H"), ws.Cells(lrow, "H")).Copy
  wscsv.Sheets(1).Range("E1").PasteSpecial xlPasteValues
  ws.Range(ws.Cells(2, "E"), ws.Cells(lrow, "E")).Copy
  wscsv.Sheets(1).Range("F1").PasteSpecial xlPasteValues
  ws.Range(ws.Cells(2, "I"), ws.Cells(lrow, "I")).Copy
  wscsv.Sheets(1).Range("G1").PasteSpecial xlPasteValues
  lrow = wscsv.Sheets(1).Columns("A").End(xlDown).Row
  wscsv.Sheets(1).Range(wscsv.Sheets(1).Cells(2, 1),    wscsv.Sheets(1).Cells(lrow, 1)).NumberFormat = "mm/dd/yyyy"
  wscsv.Sheets(1).Range("A1").Value = "Date"
  wscsv.Sheets(1).Range("B1").Value = "open"
  wscsv.Sheets(1).Range("C1").Value = "high"
  wscsv.Sheets(1).Range("D1").Value = "low"
  wscsv.Sheets(1).Range("E1").Value = "close"
  wscsv.Sheets(1).Range("F1").Value = "volume"
  wscsv.Sheets(1).Range("G1").Value = "cap"
  wscsv.Save
  wscsv.Close

  MsgBox ("Copying complete")

  End Sub

Upvotes: 1

Views: 3116

Answers (2)

Shai Rado
Shai Rado

Reputation: 33692

Resolving your error line, just use:

ActiveWorkbook.SaveAs Filename:=savedirectory, FileFormat:=xlCSV, ConflictResolution:=2

(ConflictResolution = 2 , equals xlLocalSessionChanges , read here : https://msdn.microsoft.com/en-us/library/office/ff194803.aspx

However, you could improve your code by not using ws.Activate , ActiveWorkbook and csvworkbook = ActiveWorkbook.Name. You could directly assign your wscsv (defined as workbook) to the new created workbook. See my code below how to reference all objects.

Code

Option Explicit

Sub AddNewWorkbook1(ws As Worksheet)

MsgBox ("adding new workbook for " & ws.Name)

Dim wscsv As Workbook
Dim savedirectory As String
Dim currentworkbook As String
Dim csvworkbook As String

currentworkbook = ws.Name
savedirectory = "Your Path" & "\" & ws.Name

Dim lrow As Long
lrow = ws.Columns("A").End(xlDown).Row

Set wscsv = Workbooks.Add
DisplayAlerts = False

wscsv.SaveAs Filename:=savedirectory, FileFormat:=xlCSV, ConflictResolution:=2
csvworkbook = wscsv.Name

MsgBox ("Entering copying")

' do the rest of your copy >> paste 

End Sub

Upvotes: 1

Diogo
Diogo

Reputation: 132

ActiveWorkbook.SaveAs Filename:=savedirectory, FileFormat:=xlCSV, ConflictResolution:=xlLocalSessionChanges

Could you try this way? It worked for me.

Upvotes: 0

Related Questions