Solved

# Exporting from VB to excel

Posted on 2003-03-04
Medium Priority
171 Views
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
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

LVL 3

Expert Comment

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

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

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

LVL 1

Expert Comment

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

ID: 8064166
0

LVL 3

Accepted Solution

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

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")

Application.Visible = True
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

ID: 8064642
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

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

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…
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