Visual Basic :: Import Excel File Into MSFlexgrid

Import Excel File
I like to import Excel file into sql temp table in VB. I like to select only certain records based on the criteria.
Here is the sample of my code. I only get error when I use the criteria.

Public Function UploadBankFile(sFileName As String) As Boolean
On Error GoTo Err_UploadBankFile:
UploadBankFile = False
Dim nxtRec As String
Dim adoCon As ADODB.Connection
Dim rsExcel As ADODB.Recordset
Dim rsSQL As ADODB.Recordset
Dim strSQL As String
Dim strSqlFile As String

'FileCopy sFileName, sBankFileDest
Set rsExcel = New ADODB.Recordset
Set adoCon = New ADODB.Connection
adoCon.Provider = "Microsoft OLE DB Provider For ODBC Drivers"

'Connection
adoCon.Open ("DSN=Excel Files;DBQ=" & sFileName & ";DefaultDir=C:TEMP;DriverId=22;MaxBufferSize=512;PageTimeout=5;")

If OpenSQLServerConnection = False Then
MsgBox sMessageText, vbInformation, sMessageTitle
Exit Function
End If

Set rsSQL = New ADODB.Recordset
strSQL = "Delete from tmpcheckcashed"
rsSQL.Open strSQL, cn

Set rsSQL = New ADODB.Recordset
strSQL = "Select * from tmpcheckcashed"
rsSQL.Open "tmpcheckcashed", cn, adOpenDynamic, adLockOptimistic, adCmdTable

'Retreiving Data

strSqlFile = "SELECT * FROM `Bank$`" _
& " WHERE `Bank$`.Description$`='CHECK' "

' I am getting Error at this point.
rsExcel.Open (strSqlFile), adoCon

Import Excel File
I can do it with the following code but it has some problems in that it doesn't seem to release the excel file from memory when it is finished.

Is there a line input statement or some other way of doing it?

Public Sub Add_To_FRREQ()

Dim myXL As Object
Dim dbs As Database
Dim rc As Recordset

Set myXL = CreateObject("Excel.Application")

myXL.Workbooks.Open filename:=App.Path & "FRREQ.xls"
With myXL
.Workbooks("FRREQ.xls").Activate

Set dbs = Workspaces(0).OpenDatabase(App.Path & "frreq.mdb")
Set rc = dbs.OpenRecordset("tFR_Misc_Requests")

For i = 2 To 10
.range("A" & i).Select
strActiveCell = .ActiveCell
If strActiveCell <> "" Then
rc.AddNew
rc![acctnum] = .ActiveCell
Else
Exit Sub
End If

.range("B" & i).Select
strActiveCell = .ActiveCell
If strActiveCell <> "" Then
rc![oldAcctNum] = .ActiveCell
End If

.range("C" & i).Select
strActiveCell = .ActiveCell
If strActiveCell <> "" Then
rc![pName] = .ActiveCell
End If

.range("D" & i).Select
strActiveCell = .ActiveCell
If strActiveCell <> "" Then
rc![Requestor] = .ActiveCell
End If

.range("E" & i).Select
strActiveCell = .ActiveCell
If strActiveCell <> "" Then
rc![ReqExt] = .ActiveCell
End If

.range("F" & i).Select
strActiveCell = .ActiveCell
If strActiveCell <> "" Then
rc![ReqDept] = .ActiveCell
End If

.range("G" & i).Select
strActiveCell = .ActiveCell
If strActiveCell <> "" Then
rc![ReqMgr] = .ActiveCell
End If

.range("H" & i).Select
strActiveCell = .ActiveCell
If strActiveCell <> "" Then
rc![DateReq] = .ActiveCell
End If

.range("I" & i).Select
strActiveCell = .ActiveCell
If strActiveCell <> "" Then
rc![SSN] = .ActiveCell
End If

rc.Update
Next

End With
myXL.Quit
Set myXL = Nothing
rc.Close
dbs.Close

End Sub

VBA - Import From Txt File To Excel
Hi all, Can anyone advise me on how i should go about importing contents of a txt files into excel? thanks!

Import Excel File
Hi All,

I want import a execel file into VB, then check the data, and insert to the SQL.

any good Idea?

Thanks

Kel

Import A .qif File Into Excel
I currently have an Excel program that utilizes information from .qif files typically used by programs like Quicken and Money. I like the Excel better than Quicken and Money and am looking at ways to automate the import of the downloaded .qif file from the bank into Excel. I currently have a macro (and I see other VB posted) that allows for import of comma or tab delimited files into excel, however, I cannot come up with a way to deal with the following data set where with a start and end to each item (C* and ^) with initial letters (D, N, P, T):

C*
D05/22/2005
N
PHOLLYWOOD VIDEO
T-8.79
^
C*
D05/22/2005
N
PHARRIS TEETER
T-88.50
^
C*
D05/25/2005
N
PTHE HOME DEPOT 4608
T-10.67
^

In essence, I would like the Excel macro to open the text file, parse the data, and put everthing after D in the Date column, after N in the Information column, after P in the Place column, and after T in the Amount column.

Any thoughts?

Import Excel File In Vb
please send the code or help us to write the code "Import excel file in visual basic "Actually i want to do like when we click in command button of import in visual basic then the data of excel file or an excel file fetch or import from excel file.and same for export also.

please help to write code.

seema

Import File In Excel
Hi All,

I have a script in VB within XCEL but now i want to run the same script to a bunch of files so I am creating a little program in VB.
I want to create a excel file and then import a file which is tab and space delimited.

Any help will be greatly apreciated,

Thanks

Toshi

How To Import Txt File To Excel
How do i import or open a txt file in excel.the txt file has a colon( delimiter.
could u pls help me with a code along with the references that has to be included for the code.
actually i do have macro but my system doesnt repond to the code so pls help.
here's the macro
Sub ImportTextFile()

Dim DestBook As Workbook, SourceBook As Workbook
Dim DestCell As Range
Dim RetVal As Boolean

' Turn off screen updating.
Application.ScreenUpdating = False

' Set object variables for the active book and active cell.
Set DestBook = ActiveWorkbook
Set DestCell = ActiveCell

' Show the Open dialog box.
RetVal = Application.Dialogs(xlDialogOpen).Show ("*.txt")

' If Retval is false (Open dialog canceled), exit the procedure.
If RetVal = False Then Exit Sub

' Set an object variable for the workbook containing the text file.
Set SourceBook = ActiveWorkbook

' Copy the contents of the entire sheet containing the text file.
Range(Range("A1"), Range("A1").SpecialCells(xlLastCell)).Copy

' Activate the destination workbook and paste special the values
' from the text file.
DestBook.Activate
DestCell.PasteSpecial Paste:=xlValues

' Close the book containing the text file.
SourceBook.Close False

End Sub

thanx
reshma

Import Data From Excel File Into New Tab Of Another Excel File
I am new to Visual Basic, so sorry if this is an extremely simple question.

What I want to do is create code that will prompt the user to choose an Excel file to import data from, and have this data copied to a newly created tab in another Excel file.

In other words, the user will already have an Excel file opened, and will click on a button that says something like "Click here to import new data." This will open a Windows Explorer window from which they will choose the file containing the desired information. The values contained in the selected file will be imported into a newly-created tab in the Excel spreadsheet that is already open. Preferably, the tab will have the same name as the file name from which the data is imported (i.e. the tab will be called "Q1 Revenue" if they choose "Q1 Revenue.xls" to import from.)

Thanks for the help.

Import Results From Excel File [VB6]
Given the following:
- A form [frmMain] with 3 textboxes [txt1, txt2, and txt3]
- An Excel File (text.xls) with 2 rows and 3 columns that looks like the following
NAME ID TYPE
Jon 01 Active

I need a way to import the results from the Excel file into the Textboxes.
Row[0] is only headers (Name, ID, and Type) so Row[1] holds the actual data I need to retrive.

Currently I have the following written up (load the excel file and open it):

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open(c: est.xls, true)

With oBook.Sheets(1)
.Activate
… now what? ….

Any clues?
I need a way to take those results from the Sheet and place them in the appropriate text boxes.

Of course this is very generalized, in reality there are many rows and much more columns however this will serve as a great starting point for me.

Import A Text File Into Excel Using VB
i want to be able to import data from a text file to a excel file. i know that excel has the ability to open text files using the File>Open facility, but the problem is that the data in the text files are will be going to a different cell in excel (if that makes sense)

eg The data in text file line 1 needs to go to cell C8
The data in text file line 2 needs to go to cell D58

But there are hundreds of lines of data (ARGGGHHHH!!!). But the data is in tab delimited format.

the main reason I want to do this is that it will save time as the user will not have to manually input each number. Also it will prevent me having to design humdreds of userforms. I learnt how to import from one excel file to another, but text files are a mystery to me.

Any help much appreciated

Cheers

How To Import 2 Csv Files Into An Excel File
Hi all:

I just found this forum yesterday and signed on today. I searched for solutions/hints to my problem but I did not find what I need. I hope that someone here can help me. TIA.

Problem:
I have an existing application in VB6 that I need to modify. I need to read 2 CSV files into an Excel file that I am creating. Each of these CSV files need to be imported into a different Sheet of the Excel file.

Thanks,
Dwayne

Import Excel File To Database
I'm able to print my database info to excel now I have been told it would be nice to update sertain tables in the programs database by importing information from excel to the database through the program I have created.
Anyone know how to do this?

What I am tring to do is from my program I want to click a button that says import new Manufacturers. This would take the iformation from the excel spreedsheet and import it into the database Table for Manufacturer.
The Database is an .mdb . The person I'm writing this for does not have access so he wants to be able to this through the program, so I need to know how to code it to do this.
Edited by beastcmg on 07/17/01 10:29 AM.

Need Help To Import Text File Into Excel
I am using Visual Basic 6 (cmdButton event) to import delimited text files
into Excel.

However, the problem is when I have large text files that I need to import
into Excel from Visual Basic.....e.g. files with > 65, 536 rows

I think there is a way to automatically Add new Excel worksheets on the
fly while importing the entire text file????

If someone has the code or an idea how to import large text files into
multiple Excel worksheets it woud be GREATLY appreciated!

thanks!

How To Import File From Excel Sheet
Hi All,

Is it possible to import .xls file from VB?

If yes, Can Anyone tell me HOW?

Urgent....import Excel File
Hi Friends,

Please help me. I am using VBA in excel. I know how to get VBA to open up an excel file.

However the issue is that the excel file is regenerated every few hours and as a result the file name keeps changing.

I would like to know is there any way to create code so that VBA looks in a certain folder e.g. C:\WindowsDesktopTestFolder

and opens up the last excel file in that folder i.e. the one created most recently

Basically we want to import the most recent excel file which resides in the folder without making the user type in the entire path.

I look forward to your help. Thank You

Rajesh

Import A Text File Into Excel
I want to import a text file into Excel. I have a macro in Excel that does it quite well, however I want to handle everyting from VB & not have to have an Excel macrobook with my program. THis is the code I have so far, however, I'm having a problem getting the field_info:= line correct.

Private Sub Command1_Click()
Dim objXLApp As Object

' Launch Microsoft Excel
Set objXLApp = CreateObject("Excel.Application")
objXLApp.Visible = True

With objXLApp

Workbooks.Open.Text("C:WINDOWSTEMPMP3_Export.txt", file_origin:=2, start_row:=1, _
file_type:=1, text_qualifier:=3, consecutive_delim:=False, tab:=True, _
semicolon:=False, comma:=False, Space:=False, Other:=True, other_char:="-", _
field_info:=Array(Array(1, 2), Array(2, 2)))
End With

End Sub

Import VB Module In Excel File
Hello!

I have an excel file that users will use to build their own custom version into a new file. Now I would like to insert a formatting macro into the users new custom build file, but I can't get excel to add it (Error 1004: Programmatic access to Visual Basic Project is not trusted). Here is simple version of my code, any ideas? I think I need to create a Module Object first, but I don't know how to do this...

Workbooks.Add

mac_file = ThisWorkbook.Path & "inv_plan_mac.bas"

Set VBP = ActiveWorkbook.VBProject
With VBP.VBComponents
.Import mac_file
End With

Import Excel File To Access
Does someone have some sample code for VB6 to import an existing excel file into a table in Microsoft Access?

Thank you
Leonard

Import Text From A .pdf File Into Excel
Hi guys,

I have a .pdf file, which I can open, select all the text (ctrl+A), copy (ctrl+C), and paste (ctrl+V) into Excel.

How do I do this using VB? (W95+XL97)

I've spent all day searching for this, but only found the code to open the .pdf file...

Code:Sub test()

Dim RetVal

RetVal = Shell("C:Program FilesAdobeAcrobat 5.0ReaderAcroRd32.exe /s C:Data est.pdf", 3)

'what next?

End Sub

TIA,
oddbodd

Import Excel File Into SQL Server Using VB
I am an excel file in the format like this in sheet1:
Quote:Num Name
0001 ABC
0002 EFG
. .
. .
0003 XYZ
Now I want once my VB program runs, it will convert the excel table into a SQL table. How can I do that??
May be if I can select the records in the excel file to the recordset of VB will be better, but how in both case?

Edited by - ustreddog on 5/13/2004 8:59:25 PM

VBA Excel Import Text File
Good morning people.

I am trying to import a text file into my existing spreadsheet tab. If I open a blank spreadsheet, and click File --> Open --> Filename it populated the Worksheet and changes Sheet1 to the filename (eg. Customers).

When I do the same thing in code, it creates a new spreadsheet with a Customers tab.

I want to have the imported information on the same tab and on the same spreadsheet without creating a new one each time.

I have the following code:

Sub Import()
'Dim filetoopen As Variant

ChDrive "O" 'Set Drive letter
ChDir ("O:UAOPSFINPremium Collections and AccountingOutstanding Check Letters")
filetoopen = Application.GetOpenFilename("Text Files (*.txt), *.txt, Excel Files (*.xls), *.xls, All Files (*.*), *.*")
If filetoopen <> False And filetoopen <> "" Then
 Workbooks.OpenText Filename:= _
 filetoopen _
 , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
 xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
 Comma:=False, Space:=False, Other:=True, OtherChar:="~", FieldInfo:=Array(Array(1, 1), _
 Array(2, 1), Array(3, 1), Array(4, 3), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
 Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 3), Array(13, 9), Array(14, 9), Array(15 _
 , 9), Array(16, 9), Array(17, 9), Array(18, 9), Array(19, 9), Array(20, 9), Array(21, 9), _
 Array(22, 9), Array(23, 9), Array(24, 9), Array(25, 9), Array(26, 9), Array(27, 9), Array( _
 28, 9)), TrailingMinusNumbers:=True

End Sub

Any suggestions?

Thank you,

WHEELS

EXCEL Text File Import
Hi people.

Just a quick question (hopefully).

I am creating some code to import a text file. It works fine, but it creates a new Wookbook with the file name as the tab name.

I was hoping to create the new tab in the same Workbook I am running the macro from (no need for two spreadsheets - when two tabs would surfice).

Is this possible and if so, how is this usually accomplished?

Thank you, DAVE

Import .CSV File From The Web To Excel Worksheet
Hi,

Is there a way to import a CSV file by doing a web query in excel. This is the string I am using, but its not downloading the information. Its asking me to download. I want it to download automatically and insert the values into a sheet. Any help will be appreciated. Thanks

Quote:http://finance.google.com/finance/historical?q=NYSE:XOM&output=csv

Edited by - str50 on 3/10/2008 6:38:22 PM

How To Import Excel File Sheets To Access Using VBA?
Hi,

I have an excel file with 5 worsheets. All have to be loaded to Access Database with 5 tables. The Worksheet Name is the same as the table name in Access. The tables would "add" the records from the particular excel sheet via macro.

For eg. The Excel file "To be Loaded.xls" has 5 sheets.
Sheet 1 is "procTable". The table in Access "procTable" is to add the records from the sheet using a macro. The columns are the same as well.

There is another glitch. In 2 of the tables, the primary key is the auto number by access.

Can anyone help me how to go about doing it as I am only a beginner to VBA and access?

Thanks

Is It Possible To Import One Excel File As One Cell Into Database?
Hi, all,
I have many excel files, each of them has some data and a chart. I am wandering if it is possible that in Access, we can have a table or something which we can import one individual file as one cell of the table. Or we can a access table just with the file name and the link to the access file?
If it is not possible, any suggestion to arrange these hundreds of excel files? It really looks messy to me now.

thanks.

Trying To Import Multiple .csv File Into 1 Excel Sheet
Hi guys,

This is my first visit to these forum. I'm quite new to visual basic programing and I'm trying to create a macro within Excel to import multiple .csv files at the same time.

Here are more details:

All files are in the same directory and I want that macro to look at the entire directory and get all .csv files and put it one after the other into an Excel sheet.

All files have the same size (250 lines) but I want to leave a blank space between each of the imported files.

I'm sure this must be a very simple thing to do for experts but for me, it's a nightmare. I'm really working hard, but after 3 weeks of trial an error, I give up.

Please post anything to help me. Code sample would be better.

Thanks in advance for the help.

JF.

Import Text File In Excel Using VB Code
Hi folks,
My problem is that I am trying to automate a system in which a text file is brought into a worksheet. The code I have so far stops at the "Import Text File" window looking for the "Import" or "Cancel" button to be pressed. Even if I click the "Import" button, then the Import Text Wizard comes up and I have to click "Finish". Does anybody know the commands to avoid having to press these buttons and have the file come into the worksheet without user intervention.

The file name is a variable called FileValue. Here is the code:

SendKeys "^{Home}", True
ChDir "O:BakeryOrdRecapTextFiles"

With Selection.QueryTable
.Connection = "TEXT;O:BakeryOrdRecapTextFiles" & FileValue
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2, 9, 9, 9, 1, 9, 1)
.TextFileFixedColumnWidths = Array(11, 10, 32, 5, 8, 28, 15, 13)
.Refresh BackgroundQuery:=True
End With

Import Large Text File To Excel?
Hello, Everyone:
I would like to input a text file to my Excle data sheet. It has more than one line and I want to the excel sheet has 32 column with each row. How can I do it manually and in VBA also?
Thank you very much!
Charlie

Import Excel File Into SQL Server(asap Pls..)
hi,
the task is to upload excel file into sql server. Im having a problem with using the listview. there are two listview control, one for excel and the other is for sql server. now this listviews is about to display the fields inside the selected file both excel and sql. the user can select one or more fields to import in sql fields. i can import the select field if the user select one field only. How about the multiselect i already set the property to multiselect.

this s my code for import field to field:

'lvexcel and lvdatabase=Listviews
'it seems the problem is the selecteditem property it only gets one field but how about the other selected fields?... hope anyone can help me and give code asap. im not familiar with listview control and its my firsttime doing program in excel. tnx

Code:
x=1
for i=1 to lvExcel.count
if lvexcel.listitems(x).selected=true and lvdatabase.listitems(x).selected=true then
set rs=new adodb.recordset
dim sql as string
dimsql1 as string
sql="select["& lvexcel.selecteditem.text &"] from[sheet1$]
rs.open sql,cn
set rs1=new adodb.recordset
sql1="select["&lvdatabase.selecteditem.text &"] from[user]
if not rs.eof then
y=1
do while not rs.eof then
rs1.addnew
rs1.fields(lvdatabase.selecteditem.text)=rs.fields(lvexcel.selecteditem.text)
y=y+1
rs1.update
rs.movenext
Loop
set rs=nothing

Extract Text File And Import To Excel In VB6
Hi All,

I have a text file with name, address, phone and some of unuseful line
of text. I would like to extract only name, address, and phone and put them into 3 column in excel.

My general approach is
1-Open text file and read start a loop to read in one line at a time.
[syntax??]
2-Search for key words(Name, Address, Phone) the imply the desired
string to extract [how do I "if line contains "Name" then...??
Put them into a variable string or array [syntax??] Leading blank space
may cause the error. [syntax?]
3-When all desired data is collected, write them in an excel column.

As you can tell I'm new to VB. Any and all help appreciated.

Import A Text File Into An Excel Spreadsheet Using VBA
Hi all,

I want to import the contents of a text file into an Excel Sheet. I am using the following VBA code:
VB Code:
Path = Workbooks("NITBSum_Macro.xls").Worksheets("Network").Cells(4, 2).Value Workbooks.OpenText FileName:=Path & "JT_Insurance_Report.txt", Origin:= _ xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _ Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _ , 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array _ (14, 1), Array(15, 1)) Cells(1, 1).Select Selection.EntireRow.Insert
This is working fine for most text files, however I have one file that has more than 100,000 lines, which exceeds the maximum allowed 65,000 lines in an excel document.

So my question is:
1. How can I get it to automatically create a second excel file with the remaining contents of the text file? At the moment it just returns an error when it reaches 65,000 lines.

Thanks,

Import An Excel File Into VBA...one Tricky Aspect
Hi Friends,

Please help me. I am using VBA in excel. I know how to get VBA to open up an excel file.

However the issue is that the excel file is regenerated every few hours and as a result the file name keeps changing.

I would like to know is there any way to create code so that VBA looks in a certain folder e.g. C:\WindowsDesktopTestFolder

and opens up the last excel file in that folder i.e. the one created most recently

If not is there any way using a list box or something to display the names of each of the files in that folder and have the user click on the file they want to import?.....

Basically we want to import the most recent excel file which resides in the folder without making the user type in the entire path.

I look forward to your help. Thank You

Rajesh

Import Text File Into Excel Via VB6 Code
I have a problem that is driving me nuts.

I wish to put a text file into the sheet 2 of an Excel spreadsheet as I have info in sheet 1 which will use sheet2 data. How can this be done. All my code is written in VB6 and do not want to use Excel macros. All I want is to import a text file, surely it is not that difficult I think.

Help

Import Excel File Into Crystal Report
In my crystal report I need to match all Item Numbers from an
Excel file then using these item numbers to pull other
information from the database which I have ODBC connection in
Crystal. How should I do it? Is there any way in Crystyal report
that I can build a seperate table (seperate from the ODBC
connection) using the data in the excel file? My report is due
tomorrow. Urgent help needed. Thanks a lot!

VB:How To Import The Data Of A Excel File To Access
Hi

I would like to develop an application in Visual Basic (both in VB.6 and
VB.NET) from where I could extract the data contained in a spreadsheet
(excel) to a Database in Access 2000.

Has anyone an idea how to do it so (which syntax to use ?)

Ed

Import An Excel File To An Oracle Database
Hello evetyone!

Please help me !
I am new to Oracle database. Now , I need to import an Excel file containning accounting data to an Oracle table. How can i do it ?
of course, data constraint is a problem to solve (ex : Excel don't care about primary key but Oracle do care so import will cause errors).

Thanks in advance !

duy tan

Import Char. Delimiter File Into Excel
I am trying to import text files with a '|'as a delimiter how do you do this with VB6?

Import Into Excel File Into Another Excle File
Dear all,

The below procedure imports a specified range from a closed workbook into the current workbook, and I'm very happy with this. But it has one flaw and that is: it does not take account for the "sheet" - only file and range. I have tried to modify it but I can not figure it out.

So please, can anyone show me how to tweek the code so that I also can specify which sheet to import from?

Code:
Sub Import

Dim objBBG As BLP_DATA_CTRLLib.BlpData
Dim strIndexMemb As Variant

Dim ImportFrom As Variant

ImportFrom = DataFromWorkbook("mysourcefile.xls", "D1 : D10")

Range("a16").Select

Dim Kolonne As Long
Dim Rad As Long

For Kolonne = LBound(KildeFilen, 2) To UBound(KildeFilen, 2)
For Rad = LBound(KildeFilen, 1) To UBound(KildeFilen, 1)

ActiveCell.Offset(Kolonne, Rad).Formula = KildeFilen(Rad, Kolonne)
Next Rad
Next Kolonne

End Sub

Private Function DataFromWorkbook(SourceFile As String, SourceRange As String) As Variant

Dim dbConnection As ADODB.Connection
Dim rs As ADODB.Recordset
Dim dbConnectionString As String

dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=" & SourceFile
Set dbConnection = New ADODB.Connection
dbConnection.Open dbConnectionString ' open the database connection

Set rs = dbConnection.Execute("[" & SourceRange & "]")
DataFromWorkbook = rs.GetRows ' returns a two dim array with all records in rs
rs.Close
dbConnection.Close ' close the database connection

Set rs = Nothing
Set dbConnection = Nothing

Exit Function

Set rs = Nothing
Set dbConnection = Nothing

End Function

MS Excel File Import To VB Program And Saves To A Database
MS Excel file import to VB program and saves to a database

Guys I' m trying to create a VB program that would import a ms excel file and save it to a database(like ms access)could handle large amount of data then i could edit its content through the VB program.

1. how can i get the ms excel file data and convert it or put the data to the ms access(database)?

2. is there a sample program of this that i could refer unto?

Thanks

Text File Import To Excel - Strange Behaviour !
Hi All,

I will be as brief as poss. I wanted to import comma delimited text files into an excel spreadsheet using a shortcut key macro. Each import should populate a single row with data. I didn't know how to do it so i did the obvious thing ... I defined a macro and mimiced the process I wanted to carry out and then changed the code that was created for me. The code used the 'With ActiveSheet.QueryTables.Add' routine + lots of property definitions. (See below)

The process is as follows.
i) Use control D to start macro
ii) select an appropriate text file from the system
iii) the macro moves the active cell to column A
iii) the record is populated with the data from the file and certain changes are automatically made depending on the data imported.

Most of the time the process works well. However, if i create a new row in between two other rows and use this as my import line the whole worksheet clears itself, the columns all reset to standard width and the imported data is all that is left on the worksheet ! You can't undo and the whole sheet is lost apart from the new data.

The problem similarly occurs on other occasions when the row i am trying to import onto has already had some data which has then been deleted (I think).

I have debugged the code to run through it line by line and see where the sheet clears. It is on the Refresh property of the Add command (See below). I have tried playing with the various properties using the help files but nothing seems to work. [e.g. i changed refreshstyle from xladddelete to insertentireRows as this seemed a likely candidate but no change]

Any help greatfully received.

Thanks

Magnus

The code is :

Code:
'Go find the dta file
Dim fNameAndPath As Variant
fNameAndPath = Application.GetOpenFilename _
(FileFilter:="Text Files (*.txt), *.txt", _
Title:="Select File To Be Opened")
If fNameAndPath = False Then _
Exit Sub

'Work back to column 1 ready for import
Do While ActiveCell.Column > 1
ActiveCell.Offset(0, -1).Activate
Loop

'Import data
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fNameAndPath, _ Destination:=ActiveCell())
.Name = ""
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.RefreshStyle = xlInsertEntireRows
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
'At this point the whole spreadshhet is replaced with the data on the line the macro was activated from and all else is deleted !

'Manipulate data according to content - this all works fine from this point onwards
'Generate Reference and add to field
ActiveCell() = CombineAdd()
'Move to Date Field
ActiveCell.Offset(0, 2).Activate
etc. etc.

Import An Excel File And Save In Various Tables In Oracle Using Vb6.0
hi ,
   i would be really greatful to you if you could solve my problem.i have this project in which i have to import an excel file which user has to browse and select .i need to read file,apply few validations and save it in various tables in oracle .i m using Microsoft common Dialog Control 6.0 for allowing user to select the file.im able to trap the path of the file but not able to access it.if possible please post a sample code also.
    please help me as this is very urgent
    manisha

Edited by - manisha on 9/22/2003 1:28:19 AM

How To Import An Excel File Into An Access Table With DAO Method
Hi,

I want to import an excel file into an Access table, not with the TransferSpreadSheet function but using DAO method.

This is because sometimes when I use the TransferSpreadSheet function, the table gets with a hundreds of null records before it is the first record. The help file says it's better to use DAO method instead of the function.

I'm trying to do so but have problems with the code. What I wrote is:

    Set dbs = CurrentDb()

    Set tdfLinked = dbs.CreateTableDef("ExcelTable")

    tdfLinked.Connect = "Excel 8.0;DATABASE=test.xls"

    tdfLinked.SourceTableName = ???? 'What should come here? What's the format to define the SourceTableName???

    dbs.TableDefs.Append tdfLinked

I want the table just the same as the excel file but don't know how to define de SourceTableName

I really don't know how to solve this problem and need urgent help!!!

Thanks,
Amota.

Import Excel File Into MSFlexgrid

Hello ,i would like to import an excel file into a MSFlexGrid but when I try the Datasource way it doesn't work. Does anyone has any code available?

Hello ,

i would like to import an excel file into a MSFlexGrid but when I try the Datasource way it doesn't work. Does anyone has any code available?