Visual Basic :: Help On Reading Excel Files And Then Making Data Reports Using These Excel Files

Reading/Writing Excel Files
Hi all,

I'm working on a project at the moment that uses ADO to read Excel files. (In case you're wondering why, it's faster than using Excel via automation, and also, things tend to go really wrong when the users try and run excel at the same time as this application...)

Anyway. there are 2 ways to do this. One of the uses the Jet4.0 Provider, and I can't remember what provider you use for the other one (It's definitely and OLEDB provider though).

The problem is that if I could remember which OLEDB provider I used to get this working, I only ever managed to get a read-only connection.

I can get a read-write connection with the Jet provider, but it will only return up to 255 columns and I need to return all 256 (sometimes!)

Any ideas how I can get a read-write connection that can return all 256 columns??

VBScript & Excel - Convert Tab Delimited Files To Excel Files
I am trying to write a vbscript that will open a collection of .tsv (tab-delimited) files in Excel, change the formatting of the files (i.e., insert rows, change font size, add column headings, etc.), and then save the files as fully-functional Excel files. At this point, the vbscript I have written will do all of the formatting tasks I require. It will not, however, save the .tsv files as fully-functional Excel files. I’ve tried solutions suggested in the Automating Office Applications section of MSDN’s site without any luck. It seems like I’ll need to modify

objXL.ActiveWorkbook.SaveAs (“filename”)

to specify somehow XLFormat.

I stumbled across this site while searching for a way to accomplish this task and I’m hoping that someone here will be able to help point me to a web site that includes informatino on how to get a vbscript to change the format when saving from Excel. Thanks in advance.

VB6 -- Getting Data From Excel Files ...
Hey ... I've got about 500 Excel files with data stored in them in tabular
format ... (my coworker used excel as a database).

I want to write a vb6 program that will open up these files and get the info
from each row, and store it in an MS Access database, or perhaps a
comma-delimited file.

How can I get into an excel file with VB6? I have VB6 enterprise, and I've
installed the "excel" data access components.

Thanks in advance

Extract Data From Excel Files
Is there any method to extract data from the cells of .xls files present in one folder ... say "C:/fun/1.xls, C:/fun/2.xls, 3.xls.....
And put this data into a recordset in SQL server table...

Help, Populating Data Between Excel Files
Hmmmmmm ............... interesting problem

Help, Populating Data Between Excel Files
Hi all, i've been struggling with this for a bit, and haven't yet been able to get it do what I want. What i'm trying to do is write a function in an excel sheet that looks for some cells in another sheet and then returns the values in those cells to the first sheet (in a specific range). This is what i've come up with so far:

Code:
Private Sub CommandButton1_Click()
Dim maxrows As Integer
Dim minrows As Integer
Dim rowIndex As Integer
Dim rng As String
Dim filename As String
Dim sheetname As String

'activate other worksheet

'get the max number of rows
minrows = InputBox("Please enter the minimum number of rows", "Minimum Rows")
maxrows = InputBox("Please enter the maximum number of rows", "Maximum Rows")

'set the file name to copy from
filename = Application.GetOpenFilename

Set wrkbook = Workbooks.Open(filename)

'search the first column for the value
wrkbook.Activate
For rowIndex = minrows To maxrows

If Cells(rowIndex, 1).Value = "8XX" Then

Cells.Find("8XX").Select
'set the range for the cells to be copied
rng = "C" & rowIndex & ":E" & rowIndex

Range(rng).Copy [[Whls_Repair_Scrcd_May2002_test.xls]May_Volume_Data!q22:s22]
'Range(rng).Copy wrkBook.Sheets(strSheet).Range("A1")
'Range("q39:s39").Select
'ActiveCell.FormulaR1C1 = "[WTV052602.xls]Sheet1!"

End If
Next

End Sub

the problem that occurs is that the function searches the first colomn in May_Volume_Data sheet in the workbook Whls_Repair_Scrcd_May2002_test.xls instead of the first column in the second workbook (which is called WTV.xls).

if you put a message box in the For....next loop asking for the active workbook, it returns the right workbook (WTV.xls), but it still fails to search WTV.xls for "8XX".

Does anyone have any suggetions that keeps most of this code intact? I feel I am SO close, but i can't get this one last part to work.

thanks

Inserting Data From Other Excel Files??
Can you help? I'm lost with this...

I am trying to insert data from various excel files, so that they can be sumarised in a new 'summary' file.

The source files hold costs, total metres of material etc. This needs to be summarised in the file I am trying to create.

Every source file holds the data in the same cell reference, i.e B5 is the cost, D3 is the total m of material etc.

I have started by creating a button, but cant get a macro/ VB code to do this tricky bit, which is insert a file name and path!

Once I have got this sorted the intention is that the summary cells will reference to the filename and path, and input the data required!

I am really struggeling with this. Would be a real bonus if someone can help.

Thanks very much.

Add Data To Existing Excel Files
Hi all,

I've got an excel spreadsheet with many sheets in it. I hate having to open it in excel and then click on the appropirate sheet and then add some data. I want to be able to do this by selecting some items from a drop down menu.

For example...
Toronto.xls - contains sheets DataServer, SAP Server, Print Server, Etc.
Johannesburg.xls - contains sheets DataServer, SAP Server, Print Server, Etc.
Brisbane.xls - contains sheets DataServer, SAP Server, Print Server, Etc.

- I first select a location like Toronto, Johannesburg, Brisbane -- this will basically determine which excel file I want to work on
- Based on that I want now select a sheet to add/review data (DataServer or SAP Server or Print Server)
- I then am presented with a form which asks for me 5 or 6 things and when I click OK, that info gets saved into the excel file in the next line

Can anyone help me get started?

Thanks in advance for anyone who responds.

Regards,
Adeel

Extracting Data To Excel Via CSV Files
Hi,

I have a process which extracts data to a CSV file suitable for opening in Excel.

However it isn't particuarly attractive and when I open the file all the comma seperate values are displayed in individual columns (obviously this is the way it work I understand that)

What I want to do is this..

Assume I have a database table as follows,
PersonID
PersonForename
PersonSurname
PersonDB
PersonTelephone

And it contains 10 rows

When I extract to excel is there a way to force all similar records to appear in columns e.g.

1    Dave    Wilson    01/01/2005    12345
2    John    Davidson    01/01/2004    67890
3    Mike    Teal    01/01/2003    34943

Does anyone know how to do this?

Thanks

Making Reports In DOC Files
Hi!! how can i make reports in DOC files - with VB6
Example:
i have a database(access)with 1 table where is 3 columns!
1.Company_name
2.Director_name
3.Date
i need to make report where company_name is heading1 Director_name is normal-> bold and Date is normal in the right side!! Each new record must be placed in new page!!

i already connected to the database and have the data in datagrid!

Some adives where to find turtorial about this also help!

thanks!

Extracting Data From Numerious Excel Files?
I apologize for knowing next to nothing about VB. I got thrown into this project at work after our contract programmer became too busy, or most likely he lost interest. Anyway, I'm researching on the net to give me a basis and all that’s told me is I don't stand a chance alone on this one. I'm looking to run a Macro from an Excel sheet called WORK using (OBVIOUSLY) Visual Basic! I want the macro to:

Firstly; extract data from all excel files in a specified folder. The data i stored in the same location in each excel file (say the average of column n), this will be transferred to sheet "WORK", (placed in cell B2 of sheet 1 - obviously "exceldataworkbook1" can't be overwritten by "exceldataworkbook2"). Sooo... "exceldataworkbook2" has to go in "WORK", sheet1, cell B3, and so on.

Secondly; in cell A2, I need to put to name of the workbook the average come from. Therefore: "exceldataworkbook1" goes in cell A2 and "exceldataworkbook2" in A3 ect, ect...

Sounds easy enough… Right? I would really appetite any input in to this you guys can offer... even if it’s tackling it in a different way. I feel like I’m going to drop the ball on this one and I don’t like it.

Collection Of Data From Several Excel Files That Are In Various Folders
Hello,
I have just started learning VB so I need some help with my current project.
The main purpose of the project is to collect data from various excel files
into existing excel file.
The following steps required in the project:
1. Open all xls files in various folders. For example I have directory "2002"
with 12 folders in it: January, February, etc. Each of these folders have xls
files. Those xls files are used to collect necessary data;
2. Select "output" Sheet in all these xls files;
3. Find cell "BA58" in each xls file, copy the value of the cell
4. Paste the value of the cell in File Date order into new (or existing) file
5. Close all opened files excluding the one with the new data in it.

This is what I have done so far:

Public Folder As Variant
Sub Open_Files()
Dim Folder As String
Dim fo

Windows("macro_test1.xls").Activate
Sheets("Sheet1").Select

Range("A2:A11").Select
Set List = Range("A2:A11")

For Each cell In List
Folder = cell.Value

xlfileopen = "O:QuantmktFriday Summary2002" & Folder & "* "
Workbooks.Open FileName:=xlfileopen, ReadOnly:=True, UpdateLinks:=0

Sheets("output").Select
Range("BA58").Select
ActiveCell.Copy
Windows("macro_test1.xls").Activate
ActiveSheet.Paste Destination:=Worksheets("Sheet2").Range("A1:A10")

Next
End Sub

Please help me to complete above macro and make it work.
Thank you for your assistance.

Regards,
Luda

Excel VBA - Extracting Data From Multiple Files In A Folder
I'm using VBA with Excel to take data from about 10 workbooks in the same folder, and place specific cells from each into a new workbook, on the same sheet. What would that coding look like? Your help is appreciated

Mining Data From Excel Sheet To Create CSV Files
Hey all,

I've got an Excel document that is generated from a third party programme (See ExcelDoc attached)

I'm trying to manipulate it so that a macro creates a CSV file like the one attached (see THK.csv)

I need to have the following columns in the CSV file populated using the Excel spreadsheet
Column A: Will need to have the NUMBER that is in cell B2 of the Excel file in this case the number is 0714 (need to keep the preceeding 0 too)
Column B: Will need the phone number
Column C: Will need the fax number
Column D: Will need to have the email address
Column E: Will need to have the date only
Column F: Will need to have the Order Number
Column G: Will need to have the Customer name
Column H: Will need to have the Customer telephone number
Column I: Will need to have the Customer alternative telephone number
**Column J: Will need to have the 1st line of the address (after First Address in xls doc)
Column K: Will need to have the 2nd line of the address
Column L: Will need to have the 3rd line of the address
Column M: Will need to have the 4th line of the address
Column N: Will need to be a blank cell
Column O: Will need to be a blank cell
Column P: Will need to be a blank cell
Column Q: Will need to be a blank cell
Column R: Will need to be a blank cell
Column S: Will need to be populated with the information that is after the field MyAddress: in Cell B11 of the xls document.

I've managed to get a macro that will take the information and transpose it into a new document and rename it as an CSV file but I am having a lot of problems trying to get the macro to create the CSV file in the format that I need.

If anyone can help me with this I would be very grateful. I've attached the xls document (the one that is generated and the CSV file which is the format I'm trying to achieve.)

Thanks in advance to all of you for any help that you can give me.

**This cell is generated and will have commas at the end of each line of the address and will be terminated by a period (at line 4 of the address. Sometimes there will be 4 lines to an address but sometimes there may only be 2 or three. In the case where there is no information I need to have a blank (null) value in the cell

Merge Data From Two Separate Excel Files Into A Flexgrid.
Dear very nice VB experts!

I would like to merge data from two separate workbooks into a flexgrid.
The two books contains data in a database structure, that is one line with fild names, and then the posts in sequence under it. One of the fields are named the same, and is the "key" to what should be merged. All other fields should be put into the line in the flexgrid.

I understand, or guess that this is somewhat a tricky task, but I appriciate all hints I can get.

Example of what I mean:

Book 1:

Name ID Age
Seb 1 25
Kalle 2 30
Jenny 3 6

Book 2:

ID City
1 Gothenburg
2 New York

FlexGrid:

Name ID Age City
Seb 1 25 Gothenburg
Kalle 2 30 New York
Jenny 3 6

My first thought was to treat the two books as a relationship database, and to acces it with some nice sql statement. Is this possible?

Best wishes, Sebastian.

Extracting Data Grid Files To Word And Excel....
Can anyone teach me what codes to use in extracting the data's in my datagrid to excel type and Ms word....

Retrieving Data From Closed Excel Files Through A Macro.
Hello Everyone,
How are you guys doing !
I have a code in VB which brings in cell values from various Excel files.
The code works perfectly.
We just have to give the path and the cell vaues range like A1:D20 in the macro and ...shoot there comes the data.
But there is one proble. The macro retrieves data from those excel files which have only one worksheet or tab in them.
Can please someone help modify the code, so that I get cell values(Data) from all the worksheets(Tabs) in one report with equivalent number of tabs automatically generated in the report
Thanks

The code is :

Sub Example5()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim SourceRcount As Long
Dim N As Long
Dim rnum As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FName As Variant

SaveDriveDir = CurDir
MyPath = "C:Documents and SettingsV029728DesktopTest FolderRegional"
'Chage the path here
ChDrive MyPath
ChDir MyPath

FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", _
MultiSelect:=True)
If IsArray(FName) Then
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
rnum = 1
basebook.Worksheets(1).Cells.Clear
'clear all cells on the first sheet

For N = LBound(FName) To UBound(FName)
Set mybook = Workbooks.Open(FName(N))
Set sourceRange = mybook.Worksheets(1).Range("A13:Y93")
SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Cells(rnum, "A")

' This will add the workbook name in column D if you want

sourceRange.Copy destrange

mybook.Close False
rnum = rnum + SourceRcount
Next
End If
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

Importing Data From Files With Variable Filenames Into Excel
Hi, my first post here. I hope someone can help.
I think that I have a few syntax problems. Here's the first...

I'm trying to open a number of Windows Print spool files (.spl file extension) containing text, sequentially importing each file into Excel.
Each .spl file is one page of a print job, and I want the data to be listed one page under the other, in one worksheet.
The files I'm trying to open are spooled to a folder, C:Test, with names like "3f2e121a.spl".
I'm getting "Runtime error 1004" 3f2e121a.spl could not be found" etc. when I run the code.
Am I missing something obvious?
(I've cut out a lot of formatting etc.)

Thanks

Code:Sub Import()
Dim MyBook As Workbook
Dim SpoolFile As String

Application.ScreenUpdating = False
Sheets("sheet1").Select
SpoolFile = Dir("c: est*.spl")
Do While SpoolFile <> ""

MyBook = Workbooks.Open(SpoolFile, 0, True)
   Rows("1:4").Select
    Selection.Delete Shift:=xlUp

    Range("a1").Select

       Dim UpBound As Range
Dim LowBound As Range
While ActiveCell.Value = ""
If ActiveCell.Value = "" Then
ActiveCell.Offset(1, 0).Select
ActiveCell.Activate
End If
Wend

If ActiveCell.Row > 1 Then
    If IsEmpty(ActiveCell.Offset(-1, 0)) Then
    Set UpBound = ActiveCell
    Else
    Set UpBound = ActiveCell.End(xlUp)
    End If
Else
    Set UpBound = ActiveCell
End If

If ActiveCell.Row < Rows.Count Then
    If IsEmpty(ActiveCell.Offset(1, 0)) Then
    Set LowBound = ActiveCell
    Else
    Set LowBound = ActiveCell.End(xlDown)
    End If
Else
    Set LowBound = ActiveCell
End If

Range(UpBound, LowBound).Select

Set UpBound = Nothing
Set LowBound = Nothing
    Application.DisplayAlerts = False
    Selection.Copy
    ActiveWindow.ActivateNext
    Sheets("sheet1").Select
    Range("B65536").End(xlUp).Select
    ActiveSheet.Paste
    ActiveWindow.ActivateNext
    ActiveWindow.Close


    Kill SpoolFile

    SpoolFile = Dir
Loop

       Application.DisplayAlerts = True
       Application.ScreenUpdating = True
End Sub

Visual Basic 6.0 To Extract Data From Excel Files
I hope this question isn't too plain.

I want to extract data from cells within an xls file and place it into my visual basic application. I have coded desktop applications in VB before but I haven't "opened" or "read" any xls files before.

I'm am trying to automate a process where my visual basic application will load an existing excel file into memory, read the values from the cells and then let me play with the data.

Can anyone throw me a bone? Maybe a couple lines of code to help point me in the right direction.

Thank you in advance!
james

Download Data From Excel Files Into Database Table
Hi Gurus
I wanted to know how to extract data from Excel files into a table
This activity should take place inside the class module

example

Excel has three fields
1) Employee Code
2) Employee Name
3) Employee Designation

and the data is populated in this fields

Now I have a table
T_Employee_Master
with the same fields
viz Employee_Code,Employee_Name and Employee_Designation

I want to dump the data from the excel file into the the Table_T_Employee_Master

All this actvity sud take place insidethe class module
I wud be gratefull if someone could help me out on this
with the code

Thanks and Regards
Chucks Shah

Problem Writing Data To Excel Files Using ODBC
Hi,
I derived a class from recordset and made a connection with an already created Excel file to enter and retrive data from it. I am not able to add data to excel files(w/o using SQL). Using ODBC and MFC programming I want to write in excel files. Is it possible to do so? If possible, please provide some sample code to show this.

Making Reports In Excel VBA
Hi,

I am working on a project, I am done with all of that. I am using Excel VBA
and Access as database.

The only problem I am facing is that I want to show some data through report

for example:- there is an attandence database of students. and I fill up the data in database table through user form.

So is there any way that i can show the data( like attandence for a day or for a month in this case) through report which can be called up by clicking on a button on User form.
please show up with any example code taking above mentioned consideration in mind.

Thanks

Saving Files Already Open Files In Excel And Word
I need to program the condition where as a user is attempting to save an Excel or Word file that is already open. I'm using common dialog component and don't know how I can trap this error to inform them that the same named file is already open.

Encounter Problem When Trying To Create Data Source To Access Excel Files
Hi
We encounter problem when trying to create Data Source to Access Microsoft Excel Files. The connection string is as below:

objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & XLSFile & ";Extended Properties=Excel 8.0;"

The error message is as below:

Microsoft JET Database Engine error '80004005'

Could not find installable ISAM.
pls advise......

Creating Master Excel File From Many One Page Excel Files
I am using VB6 and am able to create a master excel file of Teaching schedules by getting all files in a specific directory that start with S2004. Starting with line 26 (MaxTimes = 11 represents time slots from 8a to 10p at night, increments of 1 hr 15 min classes) is a synopsis of the classes that an instructor will be teaching. So far I can create a single sheet in the master file from all the other schedules. What I would like to do is add a copy of the individual schedules into the master file. So, Master Sheet 1 (named 'MASTER') will still contain the synopsis. Master Sheet 2 will be a copy of Person#1's schedule (named for that person), Master Sheet 3 will be a copy of Person#2's schedule (named for that person), etc. My attempt below is not working. Please help. Thanks.

Private Sub CreateMasterSchedule_Click()
Dim Filename As String, Path As String
Dim Workbook, WorkbookAll As Workbook
Dim Worksheet, WorksheetAll As Worksheet
Dim Row As Integer, RowAll As Integer, i As Integer, j As Integer
Dim SheetNumber As Integer

'create MASTER file composed of individual schedule data
Set ExcelAll = New Excel.Application
Set WorkbookAll = ExcelAll.Workbooks.Add
Set WorksheetAll = WorkbookAll.ActiveSheet
WorksheetAll.Name = "MASTER"
RowAll = 1
SheetNumber = 2

Dialog1.Show
Dialog1.Label1 = "Loading..."

' Workbook.Worksheets.Add
' Set Worksheet = Workbook.ActiveSheet

Path = "c:"
Filename = Dir(Path)
Do While Filename <> ""
If Mid(Filename, 1, 6) = "S2004_" Then
'get data from files
Set Excel = New Excel.Application
Set Workbook = Excel.Workbooks.Open(Path + Filename)
Set Worksheet = Workbook.ActiveSheet
' WorkbookAll.Sheets(SheetNumber).Select
Dialog1.Label1 = Dialog1.Label1 + Chr(10) + "Processing " + Path + Filename + "..."
Row = 1
'offset of 3 accounts for two rows at top (Name, Days) and space row between last time slot and row data
Do While Worksheet.Rows.Cells(2 * MaxTimes + 3 + Row, 1) <> ""
For i = 1 To 12
'preserve date format as 03/14/98 instead of 34768
If i = 9 Or i = 10 Then
WorksheetAll.Rows.Cells(RowAll, i) = CStr(Worksheet.Rows.Cells(2 * MaxTimes + 3 + Row, i))
Else
WorksheetAll.Rows.Cells(RowAll, i) = Worksheet.Rows.Cells(2 * MaxTimes + 3 + Row, i)
End If
Next i
Row = Row + 1
RowAll = RowAll + 1
Loop
'copy schedule onto another sheet in the MASTER file
' WorkbookAll.Worksheets.Add
'I haven't figured out how to select and copy the entire sheet yet
' For i = 1 To Row
' For j = 1 To 12
' WorksheetAll.Rows.Cells(i, j) = Worksheet.Rows.Cells(i, j)
' Next j
' Next i
' WorksheetAll.Name = Mid(Filename, 6)
'change back to MASTER list sheet to add next faculty's info
' SheetNumber = SheetNumber + 1
' WorkbookAll.Sheets(1).Select
'close faculty file
Call Workbook.Close(False)
Excel.Quit
Set Excel = Nothing
End If
Filename = Dir() ' Get next entry.
Loop
WorksheetAll.Cells.Sort Key1:=Range("A1:L" + CStr(RowAll))
' WorksheetAll.Cells

WorkbookAll.SaveAs ("c:Documents and SettingscegDesktopMASTER")
Call WorkbookAll.Close(False)
ExcelAll.Quit
Set ExcelAll = Nothing

Dialog1.Hide
End Sub

Importing Excel Files Or Text Files
When I attempt to import any excel file or text file into access, I receive an error message "Active X component can't create object". Now before I have done this function several times with complete success. However something has changed in my program which is now preventing from acclomplishing this function. Help.

Loading Files In Excel 2000 Vs Excel 2003
Is there something different when loading files using Excel 2000 vs Excel 2003? My code works under Excel 2003, but it doesn't work under Excel 2000. The program crashes.

Here's the code:

Code:
Private Sub Form_Load()

Set xlApp = New Excel.Application
If Dir$("C:PrePaid LogPrepaid-Log.xls") <> vbNullString Then
MsgBox ("Prepaid-Log.xls was successfully found.")
Set xlWB = xlApp.Workbooks.Open("C:PrePaid LogPrePaid-Log.xls")

Else
MsgBox ("Prepaid-Log.xls was not found. A new file will be created.")
Set xlWB = xlApp.Workbooks.Add
End If

End Sub

Please help.

Thanks

Emailing Excel Files Via Outlook Using An Excel Macro
Ok maybe this is an easy one for someone out there.

My goal is to write a macro within Excel to automatically grab a file, attach it to an email and send it from Outlook. I know this can be done. I currently have a copy of a macro that will do this exact sequence but through Lotus Notes instead of Outlook. My hang up has to be with the communication between the two programs.

This is what I have currently (Excel to Lotus Notes):

Const embed_attachment As Integer = 1454
Dim session As Object
Dim db As Object, doc As Object, ws As Object, rtitem As Object
Dim CurRow As String
Dim Recipient As Integer
Dim special As String, location As String, ext As String

Sheet2.Select

'Establishes Notes session using server and .nsf file
'Server and sender must be the person sending the email
server = Cells(2, 2)
Sender = Cells(3, 2)

Set session = CreateObject("Notes.NotesSession")
Set ws = CreateObject("Notes.NotesUIWorkspace")
Call ws.opendatabase(server, Sender)
Set db = session.GetDatabase(server, Sender)

'Begins the mail loop for the number of recipients
Recipient = 8
send_to = Cells(Recipient, 2)
Do
Set doc = db.CreateDocument()
doc.savemessageonsend = True
subject_out = Cells(4, 2)
Message = Cells(5, 2)
Message2 = Cells(6, 2)
special = Cells(Recipient, 3)

doc.sendto = send_to
doc.Subject = subject_out
doc.returnreceipt = "1"

Set rtitem = doc.CREATERICHTEXTITEM("Body")
Call rtitem.APPENDTEXT(Message)
Call rtitem.ADDNEWLINE(2)
Call rtitem.APPENDTEXT(Message2)
Call rtitem.ADDNEWLINE(3)

location = "h:Monthly BudgetsColumnarxls"

'Reads filenames and attaches files until it reaches the end of the file list
z = 4
file = Cells(Recipient, z)

Do
Call rtitem.EmbedObject(embed_attachment, "", location & file & ext & ".xls")
z = z + 1
file = Cells(Recipient, z)
Loop Until IsEmpty(file) = True

'Sends file, resets doc variable to empty, and advances to next recipient

Call doc.Send(False)
Call doc.Save(True, False)

Set doc = Nothing
Recipient = Recipient + 1
send_to = Cells(Recipient, 2)

Loop Until IsEmpty(send_to) = True

'Sets variable to empty to save on memory
Set session = Nothing
Set db = Nothing
Set ws = Nothing
Set rtitem = Nothing

End Sub

Help!!!!

Thanks,

Matt

Open .csv Files Into Excel Without Using Multiple Excel Applications
I need to open a serious of around 10 .csv files into excel through my VB application. I am currently using this to open each file
Set xlWkb = GetObject("C:TempFilesfileout" & ChartCounter + 1 & ".csv")

Only one has to be open at a time but, so for instance it could open the file with this (like it does then makes a chart copies the chart into word) then close that file and open the next. How do i get around the "There is a large image on the clipboard, do you want to be able to use it in other applications?" window not to pop up?

Excel Automation Help...Involves Multiple Excel Files
Hi
First of all I am new to Excel programming..............But I can learn

Here is the description of the problem i have

There are about 10 excel sheets...
These sheets contains some daily updates(sheets updated manually)

Every week i create a new excel sheet which is based on the information contained in those 10 sheets..

What I have to do is to automate the process of generating the weekly excel sheet..This weekly sheet can be updated every time a daily sheet is updated and saved...NOT all the information from the daily sheet needs to be entered into the weekly sheet...Just some data based on certain conditions..

I beleive this is possible using Macros....I have explained the problem but i don't know how to proceed ...I beleive some one will help me with this...

Presently This is what I do:

I open one of the daily sheets...Enter the required data into the newly created (if doesn't already exist) weekly excel sheet(a new excel file...)
So this involves two diffrent excel sheets(files) and i beleive recording this as a macro is not possible...

I don't know the exact macro programming but i am good at VB so i beleive I can learn fast...

This is the idea I have..

Whenever a daily sheet is updated and saved...Update the weekly sheet(file)(Programatically)

This will involve the following

Whenever a daily sheet is saved
open the weekly update sheet using macros..
Then copy the required cells from the daily sheet to the weekly sheet..And save the weekly sheet..

I beleive this can be achived using macros...

This is the theory I have in mind...I have no idea how sound it is..I beleive I can use your expertise..

Thanx

*** RESOLVED *** Error Making Multiple Excel Reports With VB
I have written a program that will take data and allow a user to generate reports. Everything is working fine except for one thing. The problem is in the attached project on frmReport2.

The Excel report will generate correctly the first time. But, when a second report is generated, an odd thing happens - the data will be posted in a new instance of excel, but the formatting will be performed on the first report. No error is displayed.

If the first report is generated and Excel is closed, a second report will put the data in a new instance of excel but not complete the formatting stuff. This time there is an error: Error number 1004 Description "Method 'Columns' of object '_Global' failed.

All I want is to be able to run reports without having to restart the application after every one.

I have tried all sorts of things, even dimming all of the excel elements as arrays. nothing I do seems to work. I am attaching the entire project for you to check. Any help will be greatly appreciated!

Edited by - doobielg on 7/13/2005 2:09:54 PM

Creating Excel Files WITHOUT Inducing Excel Itself
I want to create a program in WinCE that exports some data to an excel file and then creates a graph from it, but doesn't start excel. Can anyone help (I need dummies guide style answers please ). I'm using Visual Basic.net 2003 Pro

How To Read Excel Files From Excel Micro.
I need to write a micro in Excel to load data from Excel files. How I can do it
without open Excel file? Could you please give a example code?
Thanks in advance

Help With Opening Multiple Txts Files In A Single Excel Files With Multiple Worksheets
Hi,
I am new to using visual basic code as I usually use it for making macros inside the Excel program.

I would like to to open numerous text/ascii files into a excel file. I would like to write VB code that I can point to a directory (and it's subdirectories) to identify text files in the folder and import the data of each file into one excel file. Each separate file would be opened into a new work sheet. File1 goes to excel file worksheet 1, file2 same excel file worksheet 2 etc. etc. What is the best way to do this? Is there a script out there that already does this?
What I want to avoid is manually open up and copy each of these fields to a new or manually use the import text wizard as we will a large number of separate files with data.

This forum thread has the code which seems to do what I need but I got it to work only once and then i get errors all time.
http://www.developerfusion.com/show/1897/
Does it need to run as VB project or as a macro in excel??

microsoft has a example of what i want to do but again not in enough detail for a novice programmer
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrsamsamplespreadsheetwithtextpulledbyadonet.asp

http://www.lacher.com/examples/lacher02.htm is another example but i want to opem multiple files in one goal

After this code we have rearrange our using another excel marco and graph the data so we have a lot of work ahead of us
Thanks for your help in advance!

Frank

Reading Data From .dbf Files
Hi,

I know this must have been asked/discussed here on VB Forum, But i just want to know how it's actually done. I did search for this, but cudnt get a satisfactory guidance so far.

I am trying to read data from multiple Foxpro tables (*.dbf) files. I am thinking of using ADO, and have been able to setup connection successfully. Though theoretically this should be doable, I still cudnt figure out how to read data from multiple tables. For example, reading invoice details from tblInvoice, tblProducts, tbleCustomers and so on.

So if any of you guys got something on this, plz say something.

Thanks a lot,

VoidMain: void-seekers!

Reading Data From Files
I have a textfile a bit over 2 MB of size, and it takes painfully long to open it (almost 60 seconds). I'm using the following code to input the data:
VB Code:
Open App.path & "index" For Input As #FF Do Line Input #FF, inputString ' Read a line wholeLog = wholeLog & inputString & vbNewLine ' Append newly read line Loop Until EOF(FF) Close #FF
Is there some faster method of reading the data from the file?

-JR-

Reading Data From Files
Please Could you help me:

I want a combo box to have a list of all the txt file in a directory. I have the code to read data from 1 text file but i dont want this

Code:
Open "C: est.txt" For Input As #1
  Do While Not EOF(1)
   Line Input #1, textfile
   Combo1.AddItem textfile
  Loop
Close #1

I have tried

Code:
Open "C:*.txt" For Input As #1
  Do While Not EOF(1)
   Line Input #1, textfile
   Combo1.AddItem textfile
  Loop
Close #1

But an err comes up 'Bad File name or number'

Please can you help...
Thx

------------------------------------------------------------------------------
And when a man gets to heaven,
To saint Paul he'll tell:
1 more soldier reporting sir,
I served My Time in Hell
6 June 1944

2GB DDR400 RAM, 3.4Ghz EM64T P4 Socket 775, 200gb PATA HD 8mb cache, 500GB WD 16mb Cache (Master), 256mb 9950 ATI RADEON AGP ,19" LCD, and a 250W PSU

Running Vs6 Enterprise, running all vs8 products express edition

Creating Excel Files Without Excel !!!
Hi,

I got my program working, making excel files from other data.
But now I have a problem. I have to make the program work
on a computer that doesn't have excel installed.

On my development pc it works fine. But the other computer
says it can't create the activex object. I guess because there
are nog excel or other office program files on that computer.

Is there a way to make it work?

Reading Data From Text Files
I tried the "Complete Visual Basic 6" book and googled this topic, but when I try and run the program there is always an error. And when it seemed that I got it right, it only returned the first line of my text file. Could you please help me?

Help Needed On Reading Data Files
Hey All,
I have a program, I Dont know when it was written and exactly in which language, it is Dos Based, but when I went to program files, there was a folder for that program called Maxim, in that maxim folder, there was a sub folder called Data and in there I found a .dat file with the size of 27 MB, could someone tell me how can I read that file into another text file or make the data readable atleast. Any suggestions?

Reading Data From Non-text Files
I need to get data (in specific chunks) from a file and read it in to an array. The data is binary. I have been trying to use the fso, but I have not been able to get it to work...

There has to be a better way..... Help?!

Reading Data From Ascii Files
hi

how do i read values from an ascii file?

i need to display values of the ASCII file in a form. the values are seperated only by a space which means i cannot put them into a table, or can I?

Please help me.

thanks

max

Data Reports Are Creating Tmp Files
hi

my data reports are creating .tmp files everytime i run them
what are these tmp files
are these needed?
how do i get rid of them?

Dat Files And Data Reports Are They Compatiable????
I got a problem and I hope someone out there can answer it and/or give some sample code asl well.

I have a program that runs off multiple computers. This program accesses a database on a network. Now some of the computers has a different network drive. So I found a way around that by using '.dat' files. And having the user actually create the pathways to the database. ANd having the program reading the '.dat' file. Now everything works great so far and there has been no problems. UNtil now!

I have two data reports and I have now idea how to, if possible, to use the data reports data environment to access the '.dat' file for the appropriate pathways.

Is there anyone out there who can help me?????

Thanx in advance

Help On Reading Excel Files And Then Making Data Reports Using These Excel Files

Hello.I would like to know how to get from an excel file specific data,using Visual Basic and then how to make with them data reports!I would appreciate any help,advise.Thank you

Hello.I would like to know how to get from an excel file specific data,using Visual Basic and then how to make with them data reports!I would appreciate any help,advise.

Thank you