?
Solved

Exporting from VB to excel

Posted on 2003-03-04
9
Medium Priority
?
171 Views
Last Modified: 2010-05-01
Hi

I'm almost new to VB. I'm trying to convrt flat files into Excel format ... how can I do it?

Thanks

Mario
0
Comment
Question by:Mdevice
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 3

Expert Comment

by:YeaYuh
ID: 8063756
You can open most text files in Excel, and format if they are in csv or fixed-length form.

CSV files are where the fields of your data are seperated by a unique character such as a ',' or another character you don't use.

Fixed, is where each piece of data takes up a fixed amount of space.

Mark   12/05/06Chicago     IL
Bob    11/04/05Charleston  SC

If your text files arent in either of these forms you would have to give me an example of the format your text file is in, and also what version of Excel you are using.
0
 
LVL 3

Expert Comment

by:YeaYuh
ID: 8063770
You can open most text files in Excel, and format if they are in csv or fixed-length form.

CSV files are where the fields of your data are seperated by a unique character such as a ',' or another character you don't use.

Fixed, is where each piece of data takes up a fixed amount of space.

Mark   12/05/06Chicago     IL
Bob    11/04/05Charleston  SC

If your text files arent in either of these forms you would have to give me an example of the format your text file is in, and also what version of Excel you are using.
0
 

Author Comment

by:Mdevice
ID: 8063774
I know I can use CSV format but I would like to avoid it ... we had some problems using it due to different international configuration in different machines.

I can generate a flat file containing information in a table (separated by commas, tab, spaces ... whatever) or have it into memory and I want to convert it to an Excel file (Excel 97)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:JH0401
ID: 8064042
Hello.  Are you wanting to open these flat files, extract the information & import it into excel in a specific format?
0
 
LVL 5

Expert Comment

by:Cimperiali
ID: 8064166
0
 
LVL 3

Accepted Solution

by:
YeaYuh earned 200 total points
ID: 8064358
You need to reference the Excel library in VB.
Just go to:
Project->References->Microsofr Excel 8.0 Object Library

Program takes a text file where the data is seperated with commas, and then place each line in a seperate row.

Example Data:
Blah,123,ABC
Growl,345,DEF
Ouch,67890,GHIJKL

Private Sub Form_Load()
   Dim lineWords As Variant
   Dim appExcel As Excel.Application
   Dim wSheet As Worksheet
   Dim wbook As Workbook
   Dim X As Long
   Dim Y As Long
   
   X = 0
   
   Set appExcel = CreateObject("Excel.Application")
   Set wbook = appExcel.Workbooks.Add
   Set wsheets = appExcel.Worksheets.Add
   
   Application.Visible = True
'Change "C:\test.txt" to your file
   Open "C:\test.txt" For Input As #1
   
   'Place each line in your file in an excel row
    While Not EOF(1)
      X = X + 1
      Line Input #1, temp$
      lineWords = Parse(temp$)
     
      upper = UBound(lineWords)
      For Y = 0 To upper
         wsheets.Cells(X, Y + 1) = lineWords(Y)
      Next
   Wend
   Close #1
'Change "C:\test.xls" to excel name you want  
   ActiveWorkbook.SaveAs FileName:="C:\text.xls", FileFormat:= _
   xlExcel9795
   
   Set wbook = Nothing
   Set wsheets = Nothing
   appExcel.Quit
   Set appExcel = Nothing
   
   End
End Sub

Function Parse(fileLine As String)
  'Splits up the fileLine into an array
  Dim ArgArray() As String
  ArgArray = Split(fileLine, ",")
  Parse = ArgArray()
End Function
0
 
LVL 2

Expert Comment

by:IrishPheremone
ID: 8064642
Per this link:
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20371214.html


Excel files are tab (vbTab) delimited.

So the same way that you creat CSV files:
"Line1, data, data, data" & vbcrlf & _
"Line2, data, data, data" & vbcrlf
SaveAs "Test.csv"

You can create 'Excel readable' files by doing this:
"Line1" & vbtab & "data" & vbtab & "data" & vbcrlf & _
"Line2" & vbtab & "data" & vbtab & "data" & vbcrlf
SaveAs "Text.xls"

You can double click on the file and Excel will open it like normal.

The only thing is if the person changes the file and tyies to save, excel will inform that it is in text format and doesn't provide all the functionality of Excel documents (blah, blah, blah)... Press 'No' to save in current excel format.

Casey
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 9024144
Hi Mdevice,
It appears that you have forgotten to close this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept YeaYuh's comment(s) as an answer.

Mdevice, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept THIS comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month15 days, 8 hours left to enroll

741 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