import excel to acess-just a mess

[michellesihdu]

Hi
I am trying to import, or should say read, an excelarc to later work
wiht it for my accessDB. but this ends up in just a mess, i.e when I
look at the variable strSQLImport it's not even readable. Anyone knows
what I've done wrong.

Option Compare Database
Function RunImportExcel()

Dim strPathFile As String
Dim strFile As String
Dim strLine As String
Dim strPath As String
Dim theStr As String
Dim blnHasFieldNames As Boolean
Dim strSQLImport As String

strDir = Forms![importWhat]![dir] & "/"
strFile = Forms![importWhat]![file]
strLine = strDir & strFile & ".xls"

tblImportToSplit = strLine

Open tblImportToSplit For Input As #1
Do While Not EOF(1)
Line Input #1, strSQLImport

Thanks in advance for an anwser
/Michelle

 

[Douglas J. Steele]

Line Input is intended for text files only. Excel is not a text file.

If the data in the spreadsheet is properly structured for use in Access, you
can simply link to it.

If it's not, you may have to use Automation of Excel.

 

[michellesihdu]

Line Input is intended for text files only. Excel is not a text file.

If the data in the spreadsheet is properly structured for use in Access, you
can simply link to it.

If it's not, you may have to use Automation  of Excel.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)

Hi
I am trying to import, or should say read, an excelarc to later work
wiht it for my accessDB. but this ends up in just a mess, i.e when I
look at the variable strSQLImport it's not even readable. Anyone knows
what I've done wrong.

Option Compare Database
Function RunImportExcel()

Dim strPathFile As String
Dim strFile As String
Dim strLine As String
Dim strPath As String
Dim theStr As String
Dim blnHasFieldNames As Boolean
Dim strSQLImport As String

strDir = Forms![importWhat]![dir] & "/"
strFile = Forms![importWhat]![file]
strLine = strDir & strFile & ".xls"

tblImportToSplit = strLine

Open tblImportToSplit For Input As #1
Do While Not EOF(1)
 Line Input #1, strSQLImport

Thanks in advance for an anwser
/Michelle- Dölj citerad text -

- Visa citerad text -

Thanks for the answer.
I made it a txtx file which I got futher with
I get stutck after the line:
Set rstImport = db.OpenRecordset(strSQLImport)
--- This is the code:
Option Compare Database
Function RunImportExcel()

Dim strPathFile As String
Dim strFile As String
Dim strLine As String
Dim strPath As String
Dim theStr As String
Dim blnHasFieldNames As Boolean
Dim strSQLImport As String
Dim tblImportToSplit As String

strDir = Forms![importWhat]![dir] & "/"
strFile = Forms![importWhat]![file]
strLine = strDir & strFile & ".txt"

tblImportToSplit = strLine

Open tblImportToSplit For Input As #1
Do While Not EOF(1)
Line Input #1, strSQLImport
'SELECT T1.*, 1 AS SheetSource
'FROM [Excel 8.0;HDR=YES;IMEX=1;Database=tblImportToSplit.
[WorksheetName$A2:U65536] as T1;

strSQLImport = "SELECT tblImportToSplit.ImportData " _
& "FROM tblImportToSplit"

Set rstImport = db.OpenRecordset(strSQLImport)

 

[michellesihdu]

Line Input is intended for text files only. Excel is not a text file.

If the data in the spreadsheet is properly structured for use in Access, you
can simply link to it.

If it's not, you may have to use Automation  of Excel.

news:e3d9c393-cf18-43ee-a273-778941029c32@r36g2000prf.googlegroups.com....

Hi
I am trying to import, or should say read, an excelarc to later work
wiht it for my accessDB. but this ends up in just a mess, i.e when I
look at the variable strSQLImport it's not even readable. Anyone knows
what I've done wrong.
Option Compare Database
Function RunImportExcel()
Dim strPathFile As String
Dim strFile As String
Dim strLine As String
Dim strPath As String
Dim theStr As String
Dim blnHasFieldNames As Boolean
Dim strSQLImport As String
strDir = Forms![importWhat]![dir] & "/"
strFile = Forms![importWhat]![file]
strLine = strDir & strFile & ".xls"
tblImportToSplit = strLine
Open tblImportToSplit For Input As #1
Do While Not EOF(1)
 Line Input #1, strSQLImport
Thanks in advance for an anwser
/Michelle- Dölj citerad text -

- Visa citerad text -

Thanks for the answer.
I made it a txtx file which I got futher with
I get  stutck after the line:
   Set rstImport = db.OpenRecordset(strSQLImport)
--- This is the code:
Option Compare Database
Function RunImportExcel()

Dim strPathFile As String
Dim strFile As String
Dim strLine As String
Dim strPath As String
Dim theStr As String
Dim blnHasFieldNames As Boolean
Dim strSQLImport As String
Dim tblImportToSplit As String

strDir = Forms![importWhat]![dir] & "/"
strFile = Forms![importWhat]![file]
strLine = strDir & strFile & ".txt"

tblImportToSplit = strLine

Open tblImportToSplit For Input As #1
 Do While Not EOF(1)
  Line Input #1, strSQLImport
  'SELECT T1.*, 1 AS SheetSource
'FROM [Excel 8.0;HDR=YES;IMEX=1;Database=tblImportToSplit.
[WorksheetName$A2:U65536] as T1;

          strSQLImport = "SELECT tblImportToSplit.ImportData " _
        & "FROM tblImportToSplit"

    Set rstImport = db.OpenRecordset(strSQLImport)

continuing...........
which I believe is simply, accurate since I dunno what I’m actually
doing, I made a txt file where the columns are separated with ';''.,
semicolon.
How do I read the each word, column, in a line, which might be 4, and
separated whit ";" ?

Thanks in advance for an answer
/Michelle

 

[Douglas J. Steele]

Your code is a little confusing, since you're using Line Input to populate
the variable strSQLImport, and then you're overwriting strSQLImport on the
next line!

If you've read a line from the file into strSQLImport, you can use the Split
function to separate it into its various fields:

Dim lngLoop As Long
Dim varFields As Variant

varFields = Split(strSQLImport, ";")
For lngLoop = LBound(varFields) To UBound(varFields)
Debug.Print lngLoop & ": " & varFields(lngLoop)
Next lngLoop


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Line Input is intended for text files only. Excel is not a text file.

If the data in the spreadsheet is properly structured for use in Access,
you
can simply link to it.

If it's not, you may have to use Automation of Excel.

news:e3d9c393-cf18-43ee-a273-778941029c32@r36g2000prf.googlegroups.com...

Hi
I am trying to import, or should say read, an excelarc to later work
wiht it for my accessDB. but this ends up in just a mess, i.e when I
look at the variable strSQLImport it's not even readable. Anyone knows
what I've done wrong.
Option Compare Database
Function RunImportExcel()
Dim strPathFile As String
Dim strFile As String
Dim strLine As String
Dim strPath As String
Dim theStr As String
Dim blnHasFieldNames As Boolean
Dim strSQLImport As String
strDir = Forms![importWhat]![dir] & "/"
strFile = Forms![importWhat]![file]
strLine = strDir & strFile & ".xls"
tblImportToSplit = strLine
Open tblImportToSplit For Input As #1
Do While Not EOF(1)
Line Input #1, strSQLImport
Thanks in advance for an anwser
/Michelle- Dölj citerad text -

- Visa citerad text -

Thanks for the answer.
I made it a txtx file which I got futher with
I get stutck after the line:
Set rstImport = db.OpenRecordset(strSQLImport)
--- This is the code:
Option Compare Database
Function RunImportExcel()

Dim strPathFile As String
Dim strFile As String
Dim strLine As String
Dim strPath As String
Dim theStr As String
Dim blnHasFieldNames As Boolean
Dim strSQLImport As String
Dim tblImportToSplit As String

strDir = Forms![importWhat]![dir] & "/"
strFile = Forms![importWhat]![file]
strLine = strDir & strFile & ".txt"

tblImportToSplit = strLine

Open tblImportToSplit For Input As #1
Do While Not EOF(1)
Line Input #1, strSQLImport
'SELECT T1.*, 1 AS SheetSource
'FROM [Excel 8.0;HDR=YES;IMEX=1;Database=tblImportToSplit.
[WorksheetName$A2:U65536] as T1;

strSQLImport = "SELECT tblImportToSplit.ImportData " _
& "FROM tblImportToSplit"

Set rstImport = db.OpenRecordset(strSQLImport)
---
which I belive is simply, hmm, I mean accurate, since I'- Dölj citerad
text -

- Visa citerad text -

continuing...........
which I believe is simply, accurate since I dunno what I’m actually
doing, I made a txt file where the columns are separated with ';''.,
semicolon.
How do I read the each word, column, in a line, which might be 4, and
separated whit ";" ?

Thanks in advance for an answer
/Michelle

 

[ryguy7272]

Did you ever get this resolved? Try this method:
Option Compare Database

Function ImportRVP()
On Error GoTo ImportRVP_Err

DoCmd.SetWarnings False

'These are the Summary Reports
DoCmd.RunSQL ("DELETE * FROM [XXX your table XXX];")
DoCmd.TransferSpreadsheet acImport, 8, "XXX your table XXX",
"C:\Documents and Settings\rs\Desktop\Access Models\Up To
Access\Summary.xls", True, "Sheet1!A1:AC150"


DoCmd.SetWarnings True
'DoCmd.CloseForm "frmImport"


ImportRVP_Exit:
Exit Function

ImportRVP_Err:
MsgBox Error$
Resume ImportRVP_Exit



End Function

PS, since you are deleting data from a table, try this on backup copy of
your DB first!!

Regards,
Ryan---
--
RyGuy

Your code is a little confusing, since you're using Line Input to populate
the variable strSQLImport, and then you're overwriting strSQLImport on the
next line!

If you've read a line from the file into strSQLImport, you can use the Split
function to separate it into its various fields:

Dim lngLoop As Long
Dim varFields As Variant

varFields = Split(strSQLImport, ";")
For lngLoop = LBound(varFields) To UBound(varFields)
Debug.Print lngLoop & ": " & varFields(lngLoop)
Next lngLoop


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Line Input is intended for text files only. Excel is not a text file.

If the data in the spreadsheet is properly structured for use in Access,
you
can simply link to it.

If it's not, you may have to use Automation of Excel.

Hi
I am trying to import, or should say read, an excelarc to later work
wiht it for my accessDB. but this ends up in just a mess, i.e when I
look at the variable strSQLImport it's not even readable. Anyone knows
what I've done wrong.

Option Compare Database
Function RunImportExcel()

Dim strPathFile As String
Dim strFile As String
Dim strLine As String
Dim strPath As String
Dim theStr As String
Dim blnHasFieldNames As Boolean
Dim strSQLImport As String

strDir = Forms![importWhat]![dir] & "/"
strFile = Forms![importWhat]![file]
strLine = strDir & strFile & ".xls"

tblImportToSplit = strLine

Open tblImportToSplit For Input As #1
Do While Not EOF(1)
Line Input #1, strSQLImport

Thanks in advance for an anwser
/Michelle- Dölj citerad text -

- Visa citerad text -

Thanks for the answer.
I made it a txtx file which I got futher with
I get stutck after the line:
Set rstImport = db.OpenRecordset(strSQLImport)
--- This is the code:
Option Compare Database
Function RunImportExcel()

Dim strPathFile As String
Dim strFile As String
Dim strLine As String
Dim strPath As String
Dim theStr As String
Dim blnHasFieldNames As Boolean
Dim strSQLImport As String
Dim tblImportToSplit As String

strDir = Forms![importWhat]![dir] & "/"
strFile = Forms![importWhat]![file]
strLine = strDir & strFile & ".txt"

tblImportToSplit = strLine

Open tblImportToSplit For Input As #1
Do While Not EOF(1)
Line Input #1, strSQLImport
'SELECT T1.*, 1 AS SheetSource
'FROM [Excel 8.0;HDR=YES;IMEX=1;Database=tblImportToSplit.
[WorksheetName$A2:U65536] as T1;

strSQLImport = "SELECT tblImportToSplit.ImportData " _
& "FROM tblImportToSplit"

Set rstImport = db.OpenRecordset(strSQLImport)
---
which I belive is simply, hmm, I mean accurate, since I'- Dölj citerad
text -

- Visa citerad text -

continuing...........
which I believe is simply, accurate since I dunno what I’m actually
doing, I made a txt file where the columns are separated with ';''.,
semicolon.
How do I read the each word, column, in a line, which might be 4, and
separated whit ";" ?

Thanks in advance for an answer
/Michelle