• Status: Solved
• Priority: Medium
• Security: Public
• Views: 172

# Exporting from VB to excel

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
Mdevice
1 Solution

Commented:
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

Commented:
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 Commented:
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

Commented:
Hello.  Are you wanting to open these flat files, extract the information & import it into excel in a specific format?
0

Commented:
0

Commented:
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

Commented:
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

Commented:
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

Tackle projects and never again get stuck behind a technical roadblock.