Visual Basic, Use VB to save Excel Spreadsheet as Tab Delimited File

Boy
#1 / 5

Use VB to save Excel Spreadsheet as Tab Delimited File

Is that possible? I want to take and select a file using VB then save
it as a Tab delimited file. Any help will be greatly appreciated.
thanks in advance.

Boyd

Mon, 28 Jun 2004 00:19:53 GMT

Boy
#2 / 5

Use VB to save Excel Spreadsheet as Tab Delimited File

Quote:

> Is that possible? I want to take and select a file using VB then save
> it as a Tab delimited file. Any help will be greatly appreciated.
> thanks in advance.

> Boyd

Anyone have any ideas?

Mon, 28 Jun 2004 06:49:36 GMT

Chad Gou
#3 / 5

Use VB to save Excel Spreadsheet as Tab Delimited File

Quote:

> > Is that possible? I want to take and select a file using VB then save
> > it as a Tab delimited file. Any help will be greatly appreciated.
> > thanks in advance.
> > Boyd
> Anyone have any ideas?

Air code: This code requires the "Microsoft Excel 9.0 Object Library"
loaded (or pick an earlier version if not on Office 2000), and the
Common Dialog component placed on a form. I'm assuming the form name
is "frmDialog", replace with your form name of choice.

'****************************************************************************

Dim xlsOut as Object
Dim strLoad as String
Dim strSave as String

CommonDialog1.FileName = ""

frmDialog.CommonDialog1.DialogTitle = "SELECT EXCEL FILE TO CONVERT TO
TAB-DELIMITED"
frmDialog.CommonDialog1.Filter = "Excel Spreadsheets|*.XLS"
frmDialog.CommonDialog1.Flags = &H4 Or &H2
frmDialog.CommonDialog1.DefaultExt = ".xls"

frmDialog.CommonDialog1.ShowOpen

If frmDialog.CommonDialog1.FileName <> "" Then

strLoad = frmDialog.CommonDialog1.FileName

Set xlsOut = New Excel.Application
If xlsOut Is Nothing Then
MsgBox "Excel is not installed on this system, or is not
configured so the system can use it."
Else
xlsOut.Visible = False
xlsOut.Application.ScreenUpdating = False
xlsOut.Application.DisplayAlerts = False

xlsOut.Workbooks.Open strLoad

frmDialog.CommonDialog1.FileName = ""
frmDialog.CommonDialog1.DialogTitle = "SAVE TAB-DELIMITED FILE"
frmDialog.CommonDialog1.Filter = "Text Files|*.TXT"
frmDialog.CommonDialog1.Flags = &H4 Or &H2
frmDialog.CommonDialog1.DefaultExt = ".txt"

frmDialog.CommonDialog1.ShowSave

If frmDialog.CommonDialog1.FileName <> ""
xlsOut.ActiveWorkbook.SaveAs strSave, -4158 'xlText value
End If

xlsOut.Quit
Set xlsOut = Nothing

End If

'****************************************************************************

Tue, 29 Jun 2004 01:34:15 GMT

Boy
#4 / 5

Use VB to save Excel Spreadsheet as Tab Delimited File

Chad, thank you very much for the code. I really appraciate it.

Boyd

Quote:

> > > Is that possible? I want to take and select a file using VB then save
> > > it as a Tab delimited file. Any help will be greatly appreciated.
> > > thanks in advance.
> > > Boyd
> > Anyone have any ideas?

> Air code: This code requires the "Microsoft Excel 9.0 Object Library"
> loaded (or pick an earlier version if not on Office 2000), and the
> Common Dialog component placed on a form. I'm assuming the form name
> is "frmDialog", replace with your form name of choice.

> '****************************************************************************

> Dim xlsOut as Object
> Dim strLoad as String
> Dim strSave as String

> CommonDialog1.FileName = ""

> frmDialog.CommonDialog1.DialogTitle = "SELECT EXCEL FILE TO CONVERT TO
> TAB-DELIMITED"
> frmDialog.CommonDialog1.Filter = "Excel Spreadsheets|*.XLS"
> frmDialog.CommonDialog1.Flags = &H4 Or &H2
> frmDialog.CommonDialog1.DefaultExt = ".xls"

> frmDialog.CommonDialog1.ShowOpen

> If frmDialog.CommonDialog1.FileName <> "" Then

> strLoad = frmDialog.CommonDialog1.FileName

> Set xlsOut = New Excel.Application
> If xlsOut Is Nothing Then
> MsgBox "Excel is not installed on this system, or is not
> configured so the system can use it."
> Else
> xlsOut.Visible = False
> xlsOut.Application.ScreenUpdating = False
> xlsOut.Application.DisplayAlerts = False

> xlsOut.Workbooks.Open strLoad

> frmDialog.CommonDialog1.FileName = ""
> frmDialog.CommonDialog1.DialogTitle = "SAVE TAB-DELIMITED FILE"
> frmDialog.CommonDialog1.Filter = "Text Files|*.TXT"
> frmDialog.CommonDialog1.Flags = &H4 Or &H2
> frmDialog.CommonDialog1.DefaultExt = ".txt"

> frmDialog.CommonDialog1.ShowSave

> If frmDialog.CommonDialog1.FileName <> ""
> xlsOut.ActiveWorkbook.SaveAs strSave, -4158 'xlText value
> End If

> xlsOut.Quit
> Set xlsOut = Nothing

> End If

> '****************************************************************************

Wed, 30 Jun 2004 02:39:18 GMT

Jean Boss
#5 / 5

Use VB to save Excel Spreadsheet as Tab Delimited File

If I understand well the question, it should be simple : open your VB
destination file for OUTPUT, then use the PRINT# command to write to the
Tab delimited file. Just separate the different items using semicolons,
and insert Chr(9) where a Tab is needed.

Example:

Open "Test" For Output As #1
Print #1, "Testing a sentence"; Chr$(9); "Testing 1-2-3"
Close 1

Quote:

>>Is that possible? I want to take and select a file using VB then save
>>it as a Tab delimited file. Any help will be greatly appreciated.
>>thanks in advance.

>>Boyd

> Anyone have any ideas?

Sun, 04 Jul 2004 10:24:56 GMT