Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




Export Excel Data Into A Formatted Text File


Hi all,

I haven't done any programming yet with VBA or EXCEl. I wanted to export data from one application to another application and their database stucture is incompatible. I exported the tables into a excelsheet and now wanted to parse the data from the excel sheet and create a formatted text output into a text file.

For instance the data in the excel table is in the following format;

Firstname;Lastname;Address1;Address2;PIN;Place;Country Code;Area Code;Telephone;TeleFax
Martin;Fritz;Palmstreet 45;Second cross;22334;Someplace;45; 07 11;98 7248 927;928 73 49

and I have to parse this table and create a text file like this;

SetField($connID, "Firstname", "Martin");
SetField($connID, "Lastname", "Fritz");
SetField($connID, "Address1", "Palmstreet 45");
SetField($connID, "Address2", "Second cross");
SetField($connID, "PIN", "22334");
SetField($connID, "Place", "Someplace");
SetField($connID, "Country Code", "45");
SetField($connID, "Area Code", "711");
SetField($connID, "Telephone", "987248927");
SetField($connID, "Telefax", "9287349");

This is just an example. The original file has more than 1500 rows and its cannot be done by hand. Note that in the table, the column entries has unwanted spaces in between phone numbers or text and they should be removed and inserted in the text file.

Once I transfer the excel table into this text format i have a tool that will export and create the necessary datastucture in the new application. Since I haven't worked with VB or Excel, I am not able to do it and I don't want to learn it from scratch.

Any help would be greatly appreciated.

Regards,

Shafas




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Export ADO Recordset To A Pre-formatted Excel File
Can anyone help on how I can export data from a recordset to a pre-formatted excel? How can I place the field value from the recordset into specific cell?

Thanks in advance

Insert Data In A Text File Created By Excel And Export Them All
Hi for all
This is very hard.
I want to insert into a text file create by Excel some information like name, address and so on.
The text file is saved and opened for data inserts.
The file is opened, excel´s macro waits 'till I save the file.
Then, it copy each line of the text file and put in a excel´s row.
Please HELP MEEEEEEE!!

Hughes for all


Alex

Export Formatted Report Access->Excel
Hi I need to export a fully formatted report from Access to excel using only Access and VBA.

I am familiar with both the transferspreadsheet command and the outputTo command, but from my experience, these do not provide a fully formatted report with headings, subheadings and page and report totals.

Can anyone suggest something that would work?

Export Data To An Excel File Using VBA?
Hi,

I have a MS Access DB that produces reports based on a SQL statement that is built in code. So, once the SQL statement is built it can look something like this (when variable is printed into imediate window):

SELECT * FROM MyTable WHERE (Company = 2) AND (WorkStatus = 'IT Manager')

I then use this SQL statement to open a report (using DoCmd.OpenReport "Query_Download_Report", acViewPreview, , SQL) but now the user wants the data to be sent straight to an excel file - much like it does when the user manually exports report data to .xls file but all done within code. Can anyone help me out with this one?

Thanks

IZM

Export Data Into Excel From A VB File
I have a file that I need to extract data from but the file seems to be unreadable. It appears it is a VB file that is a fixed length file. I have tried renaming the file with an xls extension and importing as fixed length and that does not work.

Please help as the vendor of the file is not helpful AT ALL.

Export Text File To Excel
I need to create a program that will take data from a text file and export it into an Excel document. The data in the text file is in already in a columnar format, so I'm thinking I need to "grab" each column as a seperate entity in VB and export the columns into excel. I was thinking an array may be the way to go? Also, I would like to be able to delete the text file after the data has been read from it. Any ideas to get me started would be very much appreciated!!

Getting An Error Trying Export Data To An Excel File Using VBA.
Code:
Public Function ExportRequest() As String
On Error GoTo err_Handler

' Excel object variables
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim sTemplate As String
Dim sTempFile As String
Dim sOutput As String

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim lRecords As Long
Dim iRow As Integer
Dim iCol As Integer
Dim iFld As Integer

Const cTabTwo As Byte = 2
Const cStartRow As Byte = 4
Const cStartColumn As Byte = 3

DoCmd.Hourglass True

' set to break on all errors
Application.SetOption "Error Trapping", 0

' start with a clean file built from the template file
sTemplate = CurrentProject.Path & "SalesTemplate.xls"
sOutput = CurrentProject.Path & "SalesOutput.xls"
If Dir(sOutput) <> "" Then Kill sOutput
FileCopy sTemplate, sOutput

' Create the Excel Applicaiton, Workbook and Worksheet and Database object
Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Open(sOutput)
Set wks = appExcel.Worksheets(cTabTwo)
' Commented out due to using a query instead
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(qryPublicUse, dbOpenSnapshot) ' Used qryPublicUse instead of sSQL



' For this template, the data must be placed on the 4th row, third column.
' (these values are set to constants for easy future modifications)
iCol = cStartColumn
iRow = cStartRow
If Not rst.BOF Then rst.MoveFirst
Do Until rst.EOF
iFld = 0
lRecords = lRecords + 1
Me.lblMsg.Caption = "Exporting record #" & lRecords & " to SalesOutput.xls"
Me.Repaint

For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
wks.Cells(iRow, iCol) = rst.Fields(iFld)

If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
End If

wks.Cells(iRow, iCol).WrapText = False
iFld = iFld + 1
Next

wks.Rows(iRow).EntireRow.AutoFit
iRow = iRow + 1
rst.MoveNext
Loop

ExportRequest = "Total of " & lRecords & " rows processed."
Me.lblMsg.Caption = "Total of " & lRecords & " rows processed."

exit_Here:
' Cleanup all objects (resume next on errors)
On Error Resume Next
Set wks = Nothing
Set wbk = Nothing
Set appExcel = Nothing
Set rst = Nothing
Set dbs = Nothing
DoCmd.Hourglass False
Exit Function

err_Handler:
ExportRequest = Err.Description
Me.lblMsg.Caption = Err.Description
Resume exit_Here


End Function

It keeps hanging up on me at the


Code:
Me.lblMsg.Caption = "Exporting record #" & lRecords & " to SalesOutput.xls"
part. It tells me that the .lblMsg Method or Data member was not found. Any thoughts?

Export Data To Text File
Does anybody have idea how I can export data from SQL Sever to text file. I want to make new text file every time I export data.
Also I need reverse; import data from text file to SQL server.



Thanks,

Ilimax

Export Data To Text File
Hi all,
Any way to export data in a ADO recordset to a text file?

Export Data From VB6.0 To A Text File
Hi,

I'm facing a problem in exporting data through VB6.0 to a text file. How can I do that? I need to get the data and use commas as separators and display in text file.

Thanks,
Dawn

Export From A Text File To An EXCEL Document And The Other Way Around!!!
Dear All,

I have a comma separated txt file, what i want to do is to export it in an excel, can any one show me the way??? I know that i can directly import it into an excel file, just opening it with excel program,but i want to add the specific function in my program.
I would really appreciate also if u can tell how can i read from an excel file and create a text file?

Thanks in advacnce!!!!

I Want Export Data From Excel File To Oracle Database By VB 6.0 ??
Please i want help you to me

I want export data from Excel file to Oracle database by VB 6.0
in same fields on Oracle

can i do this ?

i need answer

explain :

I want make application by VB 6.0 take data from excel file then put it on oracle database .

Please Help!! Data Missing While Export To Text File
anyone please help me to check what's wrong with my coding please?! the data can be generated and export to text file but the last 2 rows of data from the db is not display. according to the data in the db there are 9 data inside, but the last item data is not generated by my program and display in the text file. how to solve this problem?? is there anything wrong with my looping?please help.



---results---
**************************************** BOF ****************************************
Production reported from : 23/09/2007 23:00 to : 24/09/2007 23:00
Report run at : Tuesday 25/09/2007 16:11
=====================================================================================

WKSFCL: BE1PE| WKSPRD: SFH6156-4-ASY| WKSROU: DIP4-MAIN| FT1OPR: 7310|

WKSLOT| OUTDTE| INPUT_| OUTPUT| REJECT| YIELD|



WKSFCL: BE1PE| WKSPRD: IL207AM-ASY| WKSROU: SO8AMATRIX| FT1OPR: 7310|

WKSLOT| OUTDTE| INPUT_| OUTPUT| REJECT| YIELD|
1|M1737D40| 1|9/24/2007 4:03:52 AM| 1|9702| 1|9603| 1|99| 1|0.9898|
2|M1738256| 2|9/24/2007 3:30:52 AM| 2|9779| 2|9679| 2|100| 2|0.9898|
3|M1738257| 3|9/24/2007 9:31:40 AM| 3|9212| 3|9061| 3|151| 3|0.9836|
4|M1738402| 4|9/24/2007 9:30:59 AM| 4|9800| 4|9701| 4|99| 4|0.9899|



WKSFCL: BE1PE| WKSPRD: LH1510-ASY| WKSROU: SSR-MAIN| FT1OPR: 7320|

WKSLOT| OUTDTE| INPUT_| OUTPUT| REJECT| YIELD|
1|M1737703| 1|9/24/2007 8:50:40 PM| 1|3346| 1|3036| 1|310| 1|0.9074|
2|M1737706| 2|9/24/2007 9:49:08 PM| 2|3235| 2|3206| 2|29| 2|0.991|
3|M1737767| 3|9/24/2007 12:19:34 PM| 3|3317| 3|3070| 3|247| 3|0.9255|

---results retrieve from database---
WKSLOT|WKSPRD|OUTDTE| INPUT_| OUTPUT| REJECT| YIELD|
M1737D40 IL207AM-ASY 9/24/2007 4:03:52 AM 9702 9603 99 0.9898
M1738256 IL207AM-ASY 9/24/2007 3:30:52 AM 9779 9679 100 0.9898
M1738257 IL207AM-ASY 9/24/2007 9:31:40 AM 9212 9061 151 0.9836
M1738402 IL207AM-ASY 9/24/2007 9:30:59 AM 9800 9701 99 0.9899
M1737703 LH1510-ASY 9/24/2007 8:50:40 PM 3346 3036 310 0.9074
M1737706 LH1510-ASY 9/24/2007 9:49:08 PM 3235 3206 29 0.991
M1737767 LH1510-ASY 9/24/2007 12:19:34 PM 3317 3070 247 0.9255
M1737727 SFH6156-4-ASY 9/23/2007 11:28:23 PM 60753 58689 2064 0.966
M1738538 SFH6156-4-ASY 9/24/2007 9:58:28 AM 30575 30040 535 0.9825


Thank you all of you for helping me with the VB error 52

How To Export Table Data Into Text File Using VB6
Hi,

By using VB6.0, I want to export database table data into text file with a tab delimited text file (i.e inside text file like, Data1|Data2|data3....etc).

My User interface has:
Drop down list box contain list of data base table name.
A path selection area, allowing the user to specify the filename, path and file type (my case text file type) for the export. This will incorporate standard Windows functionality for allowing the user to specify file type such as tab delimited text. The path will default to the xyz server upon which my project instance is running but will allow the user to navigate to any other mapped drive.

Export button when clicked, this activates the data export using the parameters provided.

I am new to this job..please give me relevant code...

Thanks in advance
Radhakrishnan
vrradhakrishnan@gmail.com

Export Access Data To Text File
Well currently i have a database (MS Access) name VMI and i'm using VB6, i already collected the data from Text File To Access, now i wan to export the Access Data to Text Format and need to rearrange the data. here is the print screen of my output and coding.

Here Is My Aceess

http://img300.imageshack.us/my.php?image=databasewu1.jpg

And i wan my result to be output like this

http://img176.imageshack.us/my.php?image=correctoutputbw8.jpg

but i end up my reslut to be output like this

http://img182.imageshack.us/my.php?image=outputyq3.jpg

here is my coding

Private Sub Command2_Click()
Dim xCntVMI As New ADODB.Connection
Dim xRstVMI As New ADODB.Recordset
Dim xRstVMI2 As New ADODB.Recordset
Dim xRstVMI3 As New ADODB.Recordset
Dim sq, sq2 As String
Dim count, count2, count3, count4 As Double
    fh = FreeFile
    txtDisplay.Text = ""
    xCntVMI.Open mColMem("DSN VMI")

    Open "C:Documents and SettingsITMy DocumentsNew FolderAnalysis
eport.txt" For Output As #fh
    sq = "select schedule from vmi group by schedule;"

    Set xRstVMI = xCntVMI.Execute(sq)

    Print #fh, Tab(1); "Inseam/Size"; Tab(13); "Color"; Tab(23); "Schedule"
        Print #fh, String(90, "-")
        count = 23
        Do While Not xRstVMI.EOF
           Print #fh, Tab(count); xRstVMI("schedule");

        If count < xRstVMI.Fields("schedule") Then
                 count = count + 8
                End If

        xRstVMI.MoveNext
       Loop
       Print #fh, Tab(1); String(count, "-")

        sq = "SELECT inseam, size, color From VMI Group By inseam, size, color Order By inseam, size;"
        sq = "SELECT inseam, size, color, Sum(total) AS SumOfTotal From VMI Group by schedule, inseam, size, color Order By schedule, inseam, size;"
       
        
        Set xRstVMI2 = xCntVMI.Execute(sq)
        Set xRstVMI3 = xCntVMI.Execute(sq)
        
        Do While Not xRstVMI2.EOF
        Do While Not xRstVMI3.EOF
        
        Print #fh, Tab(1); xRstVMI2("inseam"); Tab(3); "-"; Tab(4); xRstVMI2("size"); Tab(13); xRstVMI2("color");
        Print #fh, Tab(24); xRstVMI3("SumOfTotal");


        xRstVMI2.MoveNext
        xRstVMI3.MoveNext
        
    Loop
    Loop
    
    
    Close #fh
    Open "C:Documents and SettingsITMy DocumentsNew FolderAnalysis
eport.txt" For Input As #fh
    textfile

End Sub


Is there anyway that i can arrange the data to be output like the 2nd picture?
Hope there are someone that can help me out Thanks.



Edited by - xwings on 8/1/2007 7:54:39 PM

Export Record Set Data To A Text File Through Vb
Can anyone help to export record set data to a text file through vb.Plz help me

How Do I Export A Range Of Excel Cells As A Text File?
Greetings,

I found this much on another site:


Code:

Worksheets(1).Copy
Activeworkbook.SaveAs FileName:="file1.txt", FileFormat:=xlTextMSDOS
Activeworkbook.close false



and it works, but it saves the entire worksheet. How can I save a specific range of cells only -- as a text file?

Thanks!
DM

Get Formatted Data From Excel To VB
I have Excel Worksheet have formatted data in Every cell. I want to copy this data in VB in and Rich Text Box in the same format. How can I do so. The formatting can be different fonts, or Superscript Subscript characters.

Import / Export Of Cell Data To Text File
HI Guys,

i need a little help with this, I need to transfer multiple cell contents from multiple sheets within a workbbook to a text file, and then be able to import the data back to the relevant cells at a later date.

The cells in question will always be the same ones, but sometimes will be blank, so need to transfer blanks as well.

I intend to run this from a command button (already created) and want it to save to either a text file or '.nfo' file i want the code to choose a default name and destination, but would like the user to have the option to change this when saving,
Also when importing i need my workbook to check that the text file it is importing is for my workbook, i assume this can be done with a simple check to see if the first line has certain text in it (which can be inserted on saving the data in the first place)

Thanks

Export Landscape Data Report To Text File
I can display and print a data report in landscape mode, but cannot get the report written to a text file without receiving the dreaded "Report Width is Larger than the Paper Width" error.
I've tried: filename1.ExportReport rptKeyText, with and without setting the paper orientation to landscape for the printer, but I still get the above error message. Any other suggestions?

Excel - How To Get Formatted Data, Not Original Data Through Code
Hi all,

I am reading through an excel column, and I need to get a number from each cell. However, I don't want the original number i.e. 65.382745, I want the number the user has specified by defining the number of decimal places i.e. 2 places 65.38, 3 places 65.383.

I am currently using the sheetname.cells(row,col) property to get at the data, how do I get the formatted data?

TIA



Noogle

Edited by - original_noogle on 10/6/2003 7:59:13 AM

Trying To Export Data Form Access To An Excel File, Getting Permission Denied Error
Basically, I have this application that Gets data from a table using a query, then it uses VBA to Export that data to an Excel spreadsheet.

Previously, this had worked perfectly before, but I had to make changes to it to seperate the data into two different groups based on a status of either OT or OTA.

So I moved the function from the main form to a pop-up form that has a combo box on it for the user to select a status type. I also changed the query to check that combo box to get the criteria for the query.

Now when I tried to export the data, it gives me a "Run Time Error 70: Permission Denied".

I have tried this with the application running on the server(Both before and after the changes) and on my PC(Which I am the admin of), and I get the same error. Here is the export code:


Code:
Private Sub cmdExport_Click()
Dim Response As Integer


Response = MsgBox("Do you want to Export all of the " & cboStatus _
& " Licensee data to the Excel file PublicUseOutput.xls? Click <Yes> to Continue or <No> to cancel the request", _
vbQuestion + vbDefaultButton2 + vbYesNo, "Export Licensee Data")
If Response = vbYes Then
ExportRequest
Else
MsgBox "Export Request Cancelled", vbInformation, "Export Cancelled"
End If
End Sub

Public Function ExportRequest() As String
On Error GoTo err_Handler

' Excel object variables
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim sTemplate As String
Dim sTempFile As String
Dim sOutput As String

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim lRecords As Long
Dim iRow As Integer
Dim iCol As Integer
Dim iFld As Integer

Const cTabTwo As Byte = 2
Const cStartRow As Byte = 4
Const cStartColumn As Byte = 3

'DoCmd.Hourglass True

' set to break on all errors
Application.SetOption "Error Trapping", 0

' start with a clean file built from the template file
sTemplate = DLookup("TemplateLocation", "tblExcelLookUp")
sOutput = DLookup("PULocation", "tblExcelLookUp")
If Dir(sOutput) <> "" Then Kill sOutput
FileCopy sTemplate, sOutput

' Create the Excel Applicaiton, Workbook and Worksheet and Database object
Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Open(sOutput)
Set wks = appExcel.Worksheets(cTabTwo)
'sSQL = "SELECT tblImport.*, IIf([MailPrefence]='Personal',
'[MailingAddress],[EmpAddress]) AS PrefAddr, IIf([MailPrefence]
'='Personal',[City],[EmpCity]) AS PrefCity, IIf([MailPrefence]
'='Personal',[ST],[EmpST]) AS PrefST, IIf([MailPrefence]='Personal',
'[ZipCode],[EmpZipCode]) AS PrefZip, [FirstName] & " " & [Middle]
'& " " & [LastName] AS FullName FROM tblImport WHERE
'(((tblImport.MailPrefence)<>"none") AND ((tblImport.LicenseStatus)
'="Active"));" ' Commented out due to using a query instead
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qryPublicUseExport", dbOpenSnapshot) ' Used qryPublicUse instead of sSQL



' For this template, the data must be placed on the 4th row, third column.
' (these values are set to constants for easy future modifications)
iCol = cStartColumn
iRow = cStartRow
If Not rst.BOF Then rst.MoveFirst
Do Until rst.EOF
iFld = 0
lRecords = lRecords + 1
Me.lblMsg.Caption = "Exporting record #" & lRecords & "!"
Me.Repaint

For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
wks.Cells(iRow, iCol) = rst.Fields(iFld)

If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
End If

wks.Cells(iRow, iCol).WrapText = False
iFld = iFld + 1
Next

wks.Rows(iRow).EntireRow.AutoFit
iRow = iRow + 1
rst.MoveNext
Loop

DoCmd.Hourglass False
ExportRequest = "Total of " & lRecords & " records processed."
Me.lblMsg.Caption = "Total of " & lRecords & " records processed."

exit_Here:
' Cleanup all objects (resume next on errors)
On Error Resume Next
Set wks = Nothing
Set wbk = Nothing
appExcel.Quit
Set appExcel = Nothing
Set rst = Nothing
Set dbs = Nothing
DoCmd.Hourglass False
Exit Function

err_Handler:
DoCmd.Hourglass False
ExportRequest = Err.Description
Me.lblMsg.Caption = Err.Description
Resume exit_Here


End Function

Any thoughts?

Currency Formatted Data Not Shown In Excel Chart
My currency formatted data does not register in the below pie chart.  Any ideas?

CODE    With oXLBook.Worksheets("Sheet2").ChartObjects.Add(225, 300, 400, 250)
        .Chart.ChartType = xl3DPieExploded
        With .Chart.SeriesCollection.NewSeries
            .ApplyDataLabels Type:=xlDataLabelsShowPercent, AutoText:=True, LegendKey:=False, HasLeaderLines:=True
            .Values = Array(CCur(TotalPostage), CCur(TotalShipping))
            .XValues = Array("POSTAGE", "SHIPPING")
            .Name = "Drop Ship Cost Composition"
        End With
    End With

Output Merged Data And Formatted Text
I need to publish i.e., print or output a formatted document which includes data fields which were inserted into the document by the VB application.

If I were using Word, this would be just a routine Mail Merge exercise. But the challenge is to do this in VB. This means I need to:

1) Store and retrieve a document which is a formatted text (e.g., RTF file).
2) Have a way to insert the data field values into the document, i.e., merge my data.

I know of three ways this can be done, and want to get advise on which is the best way to accomplish this (or learn about a third way).

A) Use a commerical ActiveX control which allows the reading of RTF files, and the merging of data, and the writing of Word Doc files. For example a product like TX Text Control (http://www.textcontrol.com/). Do you know of other controls that are available? Does anyone have experience with this one?

B) Create a copy of Word from within the VB application and execute a merge and publication. This seems like it would have a huge performace overhead.

C) Roll my own, namely create a simple text file and insert proprietory bookmarks for the inserted fields. Am I reinventing the wheel here?

Each of these approaches has some pluses and minuses and I am interested in getting some expert advise on this.

Displaying A Formatted Text File.
I'm trying to display a textfile that has tabs and linefeeds in it. The problem is when I run the program it doesn't convert the tabs or linefeeds, it seems like it just prints the ascii code for them.


Code:
Sub Readfile()
Dim inline As String
Dim lenline As Long
Dim pos As String
Dim count As Long

Open "c:downloads est.txt" For Input As #1
Line Input #1, inline
Close (1)
lenline = Len(inline)
For count = 1 To 25
pos = Mid(inline, count, 1)
Debug.Print Asc(pos)
If Asc(pos) = 9 Then pos = Chr$(9)
If Asc(pos) = 10 Then pos = Chr$(10)
txtOutput.Text = txtOutput.Text & pos
Next count
End Sub

Private Sub Form_Load()
Readfile
End Sub

I've also tried vbTab and vbLf these didn't work either.

Kuno

Writing Formatted Text To A File
Hi,

I am quite new to VB and have recently ordered a copy of VB.Net BETA. Does
anyone know how to write pre formatted text to a text file?
I am writing an encryption program and need to be able to save exactly was
is in the text box into a file with no changes, as this will cause problems
on decryption. So far I have had problems with characters being appended at
the beginning and end, and have also had problems with text at the end being
cut off.
I have been able to achieve this, but that was by inputting the text
character at a time.....this is very slow.
Any one got any suggestions?
I will include the code I have at the moment (it is very messy, but I am
experimenting)

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnSave.Click
Dim myOtherStream As Stream
Dim saveFileDialog1 As New SaveFileDialog()

saveFileDialog1.Filter = "txt files (*.txt)|*.txt|All files
(*.*)|*.*"
saveFileDialog1.FilterIndex = 0
saveFileDialog1.RestoreDirectory = True

If saveFileDialog1.ShowDialog() = DialogResult.OK Then
myOtherStream = saveFileDialog1.OpenFile()
If Not (myOtherStream Is Nothing) Then
myOtherStream.Close()
End If
End If

Dim strFileName As String
strFileName = saveFileDialog1.FileName()
strFileLocation = strFileName

Dim strText As String
Dim strLine As String

'strFileName = InputBox("FileName?", "Choose a file", , , )



FileOpen(1, strFileName, OpenMode.Output)

'FileGet(1, strText)
Dim intNoCharacters As Integer
'Do While Len(txtChange.Text) > 0
'Dim strCharacter As String
'strCharacter = Mid(txtChange.Text, 1, 1)
'txtChange.Text = Mid(txtChange.Text, 2, Len((txtChange.Text) - 1))
'Dim intAscii As Integer

'intAscii = Asc(strCharacter)
'Dim strConvertInt As String

'strConvertInt = CStr(intAscii)
'Do While Len(strConvertInt) < 3
'strConvertInt = "0" & strConvertInt
'Loop
Dim strText1 As String
strText1 = txtChange.Text

PrintLine(1, strText1)

'ConvWordToAscii = ConvWordToAscii() & strConvertInt

'Loop



'txtChange.Text = strText
FileClose(1)
txtChange.Text = Trim(txtChange.Text)

End Sub

This will produce a text file, but when I open the text in Notepad i get
square characters as carriage returns i think.

Any help would be appreciated

Why Is My File Formatted, But When I Read It Into A Text Box And Print It's Not?
Here is my code: (An example record is below of the file and the output)

Private Sub Command1_Click()
Open "C: mobileregular.txt" For Input As #1

REGULAR = 0
Do While Not EOF(1)
Line Input #1, Number1
Text1.Text = Text1.Text & vbCrLf & Number1
REGULAR = REGULAR + 1
Loop
Close #1
If REGUALR <> 1 Then
Text1.Text = Text1.Text & vbCrLf & REGULAR
End If

End Sub


(underscores represent spaces)
(this is the file)


7/26/2003_____Justin_____Williams_____Motorola C332_____39.99NW_____405-412-2222_____SR
7/26/2003_____Justin_____Williams_____Motorola C332_____39.99NW_____405-412-2222_____SR
7/26/2003_____Justin_____Williams_____Motorola C332_____39.99NW_____405-412-2222_____SR
7/26/2003_____Justin_____Williams_____Motorola C332_____39.99NW_____405-412-2222_____SR
7/26/2003_____Justin_____Williams_____Motorola C332_____39.99NW_____405-412-2222_____SR
7/26/2003_____Justin_____Williams_____Motorola C332_____39.99NW_____405-412-2222_____SR



(this is the output when read and displayed in the text box)

7/26/2003___Justin_____Williams_____Motorola C332_____39.99NW_____405-412-2222_____SR
7/26/2003_____Justin____Williams_____Motorola C332_____39.99NW_____405-412-2222_____SR
7/26/2003____Justin_____Williams___Motorola C332_____39.99NW_____405-412-2222___SR
7/26/2003_____Justin_____Williams_____Motorola C332_____39.99NW_____405-412-2222_____SR
7/26/2003____Justin_____Williams_____Motorola C332___39.99NW____405-412-2222____SR
7/26/2003____Justin_____Williams_____Motorola C332_____39.99NW____405-412-2222____SR


why? can I fix this so it looks nice and tabbed out in the text box?


Justin

It Is Posible To Extract Formatted Text From A PDF File?
does anyone know how i can extract text from a PDF file (actually a report converted to PDF)?
i got many PDF files (reports) with 132 characters lenght per line

if it posible can i create a TXT file reading and writing line per line and preserving its format, even the spaces?

i need to read the reports to fill a data base file and use the data from the differents fields to get some statistics

help please

Read In A UNIX Formatted Text File..
the following vb code:


Code:

...

Do While Not EOF(intNum)
' Read a line at a time
Line Input #intNum, strOneLine

...


does not work for this particular text file, because the lines are delimited by whatever method UNIX uses (LF?) and not what dos uses (CRLF)

Any ideas on how to read such a file, line by line, without using any external libraries etc?

Formatted READ And WRITE To A Text File
Is there a way to do formatted READ and WRITE to a text file using VB. For example in fotran we have a function like
read(id,'(4i5,f10.0,i5)',err=110,end=110npnt,ntft,ntsv,ntip,damp,n411

The second parameter tells that read four integers of length 5, one float of length 10 with nothing after decimal and then one integer of length 5. At the end there is a variable list in which these values are retrieved,
Similarly for write we have
write(id,'(a80)') titl
This function writes titl to file in a 80 character long field.
How can we achieve similar functionality in VB

regards
ali.

Reading/Import Data From Excel 2003 File And Write/export To Access 2003 Database
I'm developing Database System using MS Access 2003
As the old data are all in Excel format, I'd like to add a function into the system so that operator import old data from Excel file into the Access 2003. But i do not know how to write data into Access Database using dataset object

I have write the follwing code to read data from Excel file:

Code: Public Sub import_from_excel()
Dim conn_excel As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=c:
eport
eport_10102006.xls;Extended Properties=Excel 8.0")

sqlcommand = String.Empty
sqlcommand = "Select * from ['Multiple PN$']"
Dim excel_adaptor As New OleDbDataAdapter(sqlcommand, conn_excel)
Dim excel_DS As New DataSet
excel_adaptor.Fill(excel_DS)

'For testing
Dim excel_DT As New DataTable
Dim f_ed As New excel_data
f_ed.Show()
excel_adaptor.Fill(excel_DT)
f_ed.dg_excel.DataSource = excel_DT
'conn_excel.Open()
conn_excel.Close()
       
End Sub

Reading Table Formatted Text File And Storing Variables.
Hi Guys,

    I am venturing in a new project which involves reading text files. I would like to know whats the easiest way in
to do it



Edited by - Draven2kg on 9/19/2005 2:25:22 PM

Export Excel Data
how can i get the excel data from each cell and export to access or sql server! i dont know more about the macro. i m a beginner. please help urgent. Thank you

Export Data From Excel To SQL
Hi I have to export data from Excel table to a SQL one but I'm unable to do it!
I use the following code:

Sub CommandButton1_Click()
Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;Data Source=HCNU4030Q8L;" & _
"Initial Catalog=DB_MA_UFFICIALE_V3;User ID=sa;Password="

'Import by using OPENROWSET and object name.
strSQL = "SELECT * INTO Prova FROM " & _
"OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
"'Excel 8.0;Database=C:ProvaTb_Export_SQL.xls', " & _
"[Prova$])"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff

cn.Close
Set cn = Nothing
End Sub

This code produces the following error:

Run-time error '-2147417848 (80040e14)

I think the erroe is about the line cn.Execute!

Help me please!!!

Thank you!

Export Data To Excel
im exporting my data from ms access to excel file. i want to know if there is any other way to export the data. if the other party have no ms office install, and i using excel to export the data. will there be any error?

Is there anyway to export the data,and the users did not need to install the ms office, but the layout of the data is like a spreadsheet?

if i need to package my application to support excel, what are the files that i have to package in order for the excel to work.

thanks.

Export Data To Excel HELP HELP.
Okay, so I am pulling 2 fields from Active Directory, UserID and Name, and want to send it to excel and separate each field into 2 columns:

ex: A | B
UserID Name
UserID2 Name2

And I cant get it to place those variables in the cells with the way I have the code written. It would be easier if VB could automate the whole process and not even bring excel up, just execute in the background, and save it.

Is that possible? Below I have placed my code. Please help, and thanks in advance!!
VB Code:
Private Excel As Excel.Application Private Sub Command14_Click() 'EXCEL INSTANCE IN BKGD Dim xlsApp     Set xlsApp = Excel.Application    With xlsApp        .Visible = False        .Workbooks.Add On Error GoTo ErrHandler Text1.Text = ""Text1.Text = "ALL USERS WITH PWNE:" & vbCrLf & vbCrLf Dim strSEC As String Set objCommand = CreateObject("ADODB.Command")Set objConnection = CreateObject("ADODB.Connection")objConnection.Provider = "ADsDSOObject"objConnection.Open "Active Directory Provider"objCommand.ActiveConnection = objConnectionstrBase = "<LDAP://dc=my,dc=server,dc=com>" strFilter = "(&(objectCategory=person)(objectClass=user)" _& "(userAccountControl:1.2.840.113556.1.4.803:=65536))" strAttributes = "sAMAccountName,cn"strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"objCommand.CommandText = strQueryobjCommand.Properties("Page Size") = 1000objCommand.Properties("Timeout") = 30objCommand.Properties("Cache Results") = TrueSet objRecordset = objCommand.Execute Do Until objRecordset.EOF    strName = objRecordset.Fields("sAMAccountName").Value  strCN = objRecordset.Fields("cn").Value   strSpace = vbCrLf    strOutputData = strCN & ",  userID: " & strName  Text1.Text = Text1.Text & strOutputData & vbCrLf  objRecordset.MoveNext  ' THESE ARE THE ONLY COMMANDS I KNOW FOR EXCEL' Also, I need it to make sure it doesnt just place the last entry and actually' Reports out all users and usernames         .ActiveCell.Value = strCN        .Range("B1").Value = strNameLoopobjConnection.CloseErrHandler:MsgBox "Cannot connect to AD"End Sub








Edit: Added [vbcode][/vbcode] tags for more clarity. - Hack

Export Data To Excel
Guys anyone got any info on how to export data from vb datagrid to excel format?

Export Data To Excel
ok, ive searched the forums for like half an hour now and cant find this

i have a vb program that pretty much does calculations based on user inputs for the viability of an investment. It puts the results of these in labels

Now i also have an excel spreadsheet made up that does the same thing. I was wondering if attached to a button on the vb program is it possible to export the numerical vales from a label to a specific cell in an existing spreadsheet but rename the spreadsheet so it cannot be overwritten ie, it will always be blank and a new one will have user data?

Thanks

also, where can i learn more about vb/excel integration as im sure u dont want to answer all my basic questions

How To Export Data From SQL To Excel Using VB ?
Dear

I want to export my data from SQL Server to Excel file using VB at run time.

This is my code :

If MyR.State = adStateOpen Then
MyR.Close
End If
MyR.Open "select number,name from test order by number", MyConn, adOpenKeyset, adLockOptimistic, adCmdText
If MyR.RecordCount > 0 Then
MyR.MoveFirst
Wk.Visible = True
Set objBook = Wk.Workbooks.Add
Set objsheet = objBook.Worksheets(1)
objsheet.Name = "Inspection Data"
objsheet.Cells(1, 1).Formula = MyR.Fields(0).Name
objsheet.Cells(1, 1).Font.Bold = True
objsheet.Cells(1, 2).Formula = MyR.Fields(1).Name
objsheet.Cells(1, 2).Font.Bold = True
j = 2
While Not MyR.EOF
With objsheet
.Cells(j, 1) = Trim(MyR!number)
.Cells(j, 2) = Trim(MyR!name)
End With
j = j + 1
MyR.MoveNext
Wend
End If


Someone can tell me what's worng with my code, when i run my program, microsoft office excel will be open it.

Best Regards

Long

Export Data To Excel From VB
Hi,
I'm trying to copy information from VB6 to MS Excel which works fine until I wanted to copy information from a MSChart object. I have placed the information on the clipboard and if I manually past the information from within Excel it works just fine, but what syntax do I use to have the information past from within VB?

This is what I have used so far:
Set ExcelSheet = obExcelProg.Sheets(intSheetno)
'Display the graph's heading
ExcelSheet.Cells(1, 1).Value = lblGraphHeading.Caption
'Copy the Chart information
chGraph.EditCopy
'Paste the information into Excel
???????

I hope someone can help me out here,
Thank you!

Export Data Into Excel
Hello

I would like to export VB data automatically into an excel spreadsheet
creating both tabular data and a chart in Excel.

Could someone explain how this is done please ?
DDE ? OLE ?

Thanks in advance.

Vbr,
Darren

Darren Logan BSc (Hons)
Development engineer

Export Data To Excel
Hi,

I was wondering if anyone knows how to export different Access tables to one excel workbook, putting each table into separate sheet within the book.

Thanks.



Exporting An Access Table To A New Excel SS And Open Excel File Afetr Export
Hi,

Can some help me with writing the code to Export a table from Acces into a new excel spreadsheet, then open an instance of excel to view the newly exported table? I can get the file to export but I am having trouble with the code to open Excel, thanks for you help. See code below..

Private Sub Command26_Click()

DoCmd.TransferSpreadsheet transfertype:=acExport, _
        tablename:="newreqtable", _
        FileName:="F:UserdataSelectionData.xls", hasfieldnames:=True, _
        Spreadsheettype:=8
        
MsgBox "Export is Complete", vbOKOnly, "Selection Data Export"

MsgBox "Your new file is located on F:UserdataSelectionData.xls", vbOKOnly, "Data Export Location"

'This is where I need to have excel open the file for the client.

End Sub

Data From Excel To Text File Using VB
I am trying to extract data from an Excel spreadsheet to a text file using VB 6.0. This is my first time to use VB with Excel, so for now I am just trying to extract the data into a list box. After looking through the post this is what I have so far.


Code:
Private Sub cmdExecute_Click()

Dim oExcel As Excel.Application
Dim oWB As Excel.Workbook
Dim oWS As Excel.Worksheet
Dim IRow As Long

Set oExcel = CreateObject("Excel.Application")
Set oWB = oExcel.Workbooks.Open("c:win32app3dsBechtel3ds.xls")
Set oWS = oExcel.ActiveSheet
oExcel.Visible = False

lstList1.Clear
Do While oWS.Cells(IRow, 1) <> "" 'getting error
lstList1.AddItem (oWS.Cells(IRow, 1))
IRow = IRow + 1
Loop

Cleanup:
Set oWS = Nothing
If Not oWB Is Nothing Then oWB.Close
Set oWB = Nothing
oExcel.Quit
Set oExcel = Nothing
End Sub
The error I get is:
Run-time error '1004':
Application-defined or object-defined error

Any suggestions or help would be greatly appreciated.

Write Data From Text File To A Excel File????
i have a data in text file (file is attached). i want write the data to a excel file.How to write the code???

Write Data From Text File To A Excel File????
i have a data in text file (file is attached). i want write the data to a excel file.How to write the code???

Export Data From Excel Into Access
Hi everyone, I'm kind of stuck. I found an example of export Excel data into an existing table in Access. I have data in cells C34:O34, just one line that I need to update into an Access table. The table is called "EXP_Exceptions", the database is called: "Exceptions.mdb". The column headers are exactly as they appear in the database. The following code will run sometime with no errors, however the table doesn't update:


Code:
Sub UpdateAccess()
Dim myAccess As Access.Application
Set myAccess = CreateObject("Access.Application")

With myAccess
.OpenCurrentDatabase "C:Documents and Settingsdmorri18DesktopExceptions.mdb"
.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "EXP_Exceptions", _
"C:Documents and Settingsdmorri18DesktopQAAgentChecker.xls", True, "C34:O34"
'.CloseCurrentDatabase
End With

'Set myAccess = Nothing

End Sub
Other times I do get an error and it'll say: "Field "jpyciak" doesn't exist in destination table 'EXP_Exceptions.' That is a value in cell C34, it's title is right above it in cell C33 which is "CDSID" which is a column in the table.

I'm not sure what I am doing wrong. Does anyone have either a fix for this code, or a better way to export data from Excel into a table in Access??

Thanks,

Dave

Export Data From Excel To Project
Hello!

I want to make a time project in Microsoft Project.

I have a list and report with dates and times duration of task to in a excel sheet.

It's possible to pass this information to Microsoft Project directly by VBA ?

Export Data From Excel To Word
Hello,

I want to export an name and adres (see below).

Piet Pieters
Klompstreet 45
etc etc

This name and adres is in MSexcel and I want to export it to MSword. Is this possible, and can anybody help to make a macro in word or excel to do this?

Copyright 2005-08 www.BigResource.com, All rights reserved