Solved

Open a csv and SavAs xls

Posted on 2001-06-29
17
501 Views
Last Modified: 2006-11-17
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
0
Comment
Question by:cb168
  • 6
  • 4
  • 3
  • +3
17 Comments
 
LVL 13

Expert Comment

by:cri
ID: 6238054
'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) ??
0
 

Author Comment

by:cb168
ID: 6238390
The error that comes is:

Runtime error "1004"

Application-defined or Object-defined error

0
 

Author Comment

by:cb168
ID: 6238463
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.


0
 

Author Comment

by:cb168
ID: 6238477
The strfile  does not exist.
The strPath get set when I enter the procedure.

I not sure what I need to do from here.


0
 
LVL 13

Expert Comment

by:cri
ID: 6238549
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
 
 
0
 

Author Comment

by:cb168
ID: 6238667
The actual strCSVFileName prints out the full path.
Then I get the error message.
0
 
LVL 13

Expert Comment

by:cri
ID: 6239514
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.
0
 
LVL 3

Expert Comment

by:eal4
ID: 6240040
csv is a text file, have you tried to just open it as a text file without the code? Just a suggestion.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 13

Expert Comment

by:cri
ID: 6240219
a)
0
 
LVL 8

Expert Comment

by:Navid
ID: 6241218
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?
0
 
LVL 8

Expert Comment

by:Navid
ID: 6241220
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
0
 
LVL 8

Expert Comment

by:Navid
ID: 6241235
cb168:
I posted a point question for you. Please reply:
http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=msoffice&qid=20143919
0
 
LVL 7

Accepted Solution

by:
Nosterdamus earned 100 total points
ID: 6242078
Hi cb168,

I adjusted your code to check for file existance.

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"
 
 if dir(strCSVFileName) = "" then  'File does not exist!!!
      MsgBox "File " & strCSVFileName & " Does not exist!", _
      vbCritical, "Error"
 else

     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
 
   End If
PROC_EXIT:
 Exit Function

PROC_ERR:
 ExcelCreateWorkbook = False
 Resume PROC_EXIT

 Set newWB = Nothing
 Set newWkSht = Nothing
 
End Function



Hope this helps,

Nosterdamus
0
 

Author Comment

by:cb168
ID: 6244151
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.

0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6244368
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 http://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 http://www.experts-exchange.com/jsp/qShow.jsp?ta=msoffice&qid=20143346

cb168
</Example>


Hope this helps,

Nosterdamus
0
 

Author Comment

by:cb168
ID: 6264301
I hope I have not offended anybody nominating Nosterdamus the points.
0
 

Expert Comment

by:costello
ID: 6415585
Force accepting contribution by Nosterdamus, which was, I believe the intention of cb168.

costello
Community Support Moderator @ Experts-Exchange
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now