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

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
Asked:
Mdevice
1 Solution
 
YeaYuhCommented:
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
 
YeaYuhCommented:
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
 
MdeviceAuthor 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
JH0401Commented:
Hello.  Are you wanting to open these flat files, extract the information & import it into excel in a specific format?
0
 
CimperialiCommented:
0
 
YeaYuhCommented:
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
 
IrishPheremoneCommented:
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
 
DanRollinsCommented:
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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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