asked on

Open a csv and SavAs xls

Hi

I am trying to open a csv in Excel 2000 vba code and save
it as xls. It keeps reporting an error.
Can anybody tell what I doing wrong in my code.
All I want to do is open csv files and save as xls workbook.

Thanks in advance.

Function ExcelCreateWorkbook(strFile As String) As Boolean
' Comments : Creates and saves an empty Excel file
' Parameters: strFile - name to save as
' Returns : True if successful, False otherwise
'

Dim newWB As Workbook
Dim newWkSht As Worksheet
Dim strCSVFileName As String
Dim strPath As String

strPath = ThisWorkbook.path

On Error GoTo PROC_ERR

strCSVFileName = strPath & "\" & "SessionCount.csv"

Workbooks.OpenText _
filename:=strCSVFileName, Origin:=xlMSDOS, _
StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlTextQualifierDoubleQuote, _
ConsecutiveDelimiter:=True, Comma:=True, _
FieldInfo:=Array(Array(1, 4), Array(2, 2), Array(3, 3), Array(4, 2))

Set newWB = ActiveWorkbook
Set newWkSht = ActiveSheet

Application.ScreenUpdating = False

Application.ActiveSheet.SaveAs _
filename:=strFile, _
FileFormat:=xlWorkbookNormal

ExcelCreateWorkbook = True
ActiveWorkbook.Close

Application.ScreenUpdating = True

PROC_EXIT:
Exit Function

PROC_ERR:
ExcelCreateWorkbook = False
Resume PROC_EXIT

Set newWB = Nothing
Set newWkSht = Nothing

End Function

cri

'Rem' your On Error to see what the error is. Possible causes:
a) ThisWorkbook not saved yet, therefore strPath=""
b) strFile.xls exist already
c) ??

cb168

ASKER

The error that comes is:

Runtime error "1004"

Application-defined or Object-defined error

cb168

ASKER

Has anybody got any VBA code that will do
the job.

All my variables are set all I need is to be able to save
the workbook in code and close it.

cb168

ASKER

The strfile does not exist.
The strPath get set when I enter the procedure.

I not sure what I need to do from here.

cri

If the _calling_ workbook never was saved (or was moved) then

strCSVFileName = strPath & "\" & "SessionCount.csv"

will fail

a) Add:
strCSVFileName = strPath & "\" & "SessionCount.csv"
'Testing only to see what is searched for
Msgbox strCSVFileName

b) Also, check whether you really have a file SessionCount.csv in the directory

cb168

ASKER

The actual strCSVFileName prints out the full path.
Then I get the error message.

cri

Strange, your code worked for me (after saving the workbook...)

Sorry to re-ask: You sure the specified csv file is there and of the specified format ? 1004 are run-time errors.

a) Does it work when you do it manually ? If not, the converter might not work.

b) How far does the code actually get ? Use the debugger or paste some msgboxes to find out.

eal4

csv is a text file, have you tried to just open it as a text file without the code? Just a suggestion.

cri

Navid

Hi cb168!
You codes are 100% correct. It looked fine bu I also tryed it on mine. Works fine for me too.
As asked by Cri, are you able to perform the task manually?

Navid

To be honest I should thank you and give you points for the script because I was working on a similar one! You saved me a lot of time! hehehe

Navid

cb168:
I posted a point question for you. Please reply:
https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=msoffice&qid=20143919

ASKER CERTIFIED SOLUTION

Nosterdamus

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cb168

ASKER

Thanks a lot guys.

I have rebooted my machine and things are working fine.

CRI and Nosterdamus have both contributed to my problem.
I'm not too sure who to give the points to. Can anybody
make a few sugestions.

Nosterdamus

Hi cb168,

I'm glad that the problem is solved.

Regarding the points, you can select one of the following options:

1. award the points to the expert howhelped you most.

2. Split the points between the experts how helped you most. In order to do that, you have to post a 0 (zero) points question at the Cummunity Support (CS) Topic Area (TA), asking to split the points for the question (you can find the link at the left window).
After a moderator will reduce the points for this question, you should accept on of the expert's comments as an answer, to award to points and to PAQ this Q.
In addition, you should post another question (with the ammount of the points you whish to award) with subject as: "Points for XXXX for helping in Q 20143346" in the MS Access TA, for the other expert to collect. Once the expert proposes an answer, you should accept it to award the points and PAQ the question.
It is also recomended to post a comment in this Q with the link to the "Points for..." question. This way it is much easier for the other expert to locate and collect.

An example of 0 points question at CS TA:

<Example>
Subject: Split points between experts

Hi CS,

In question https://www.experts-exchange.com/jsp/qShow.jsp?ta=msoffice&qid=20143346 I whould like to split the points between two experts.
Please reduce the points in this question from XX to YY.
I will post a new "Points for..." question, with (XX-YY) points for the other expert to collect.

Thanks,

cb168
</xample>

An example for a "Points for..." question:

<Example>
Subject: Points for XXXX for helping in Q 20143346

Hi XXXX,

Thanks for your help in Q https://www.experts-exchange.com/jsp/qShow.jsp?ta=msoffice&qid=20143346

cb168
</Example>

Hope this helps,

Nosterdamus

cb168

ASKER

I hope I have not offended anybody nominating Nosterdamus the points.

costello

Force accepting contribution by Nosterdamus, which was, I believe the intention of cb168.

costello
Community Support Moderator @ Experts-Exchange