convert text file

G

geebee

hi,

i was not sure what forum to pot this on, so i figure this would be a good
start, even for redirection pusposes. Users should be able to upload excel
data into a table. then this table information should be exported to a .csv
file, to get something like:

67.108.115.178,199.169.192.157,66.109.83.6,66.109.83.7

where te different ip addresses are separated by a comma. i tried doing
this, but i am getting each of the IP addresses on different lines of a .txt
fle, like this:

212.138.47.11
135.196.231.255
65.217.232.2
129.33.119.25
68.143.150.50
65.218.126.98
162.58.82.244
161.215.18.51
201.147.134.225
209.208.169.98

does anyone know how i can avoid getting eah IP adress on a different line,
and instead get them on one line, etc, or separated by a comma instead of by
a line?

thanks in advance,
geebee
 
K

Klatuu

Is the data from Excel coming in on one line, or one line per IP?
How is it stored in your table?
 
G

geebee

the date from excel is sotred as one IP address pr row. the data in access
is stored one IP address per table row.
 
K

Klatuu

Hopefully, you can do some VBA coding. This is what it will take. You will
need to create a recordset that contains the IP addresses you want to put
together, then you will need to write that out to a text file, but use the
extension of csv. This will cause Excel to see each IP in a different cell
on the same row.

Dim dbf as Database
Dim rst as Recordset
Dim strIPList as String
Dim lngRecCount as Long

set dbf = CurrentDb
set rst = dbf.MyTableName
If rst.RecordCount = 0 Then
MsgBox "No Data to Process"
GoTo MyExit
Else
rst.MoveLast
rst.MoveFirst
End If

For lngRecCount = 0 To rst.RecordCount -1
strIPList = strIPList & rst![IpAddress] & ","
Next lngRecCount

strIPList = Left(strIPList, Len(strIPList) -1) ' Takes off the last comma

' Now write it to a text file
 
G

geebee

thanks. i am having one problem though: when I type

Dim dbf As Database

the "database" option does not how up. however, the "dataaccesspage" option
does show up.

how can i get "database" to show up?

thanks in advance,
-geebee

Klatuu said:
Hopefully, you can do some VBA coding. This is what it will take. You will
need to create a recordset that contains the IP addresses you want to put
together, then you will need to write that out to a text file, but use the
extension of csv. This will cause Excel to see each IP in a different cell
on the same row.

Dim dbf as Database
Dim rst as Recordset
Dim strIPList as String
Dim lngRecCount as Long

set dbf = CurrentDb
set rst = dbf.MyTableName
If rst.RecordCount = 0 Then
MsgBox "No Data to Process"
GoTo MyExit
Else
rst.MoveLast
rst.MoveFirst
End If

For lngRecCount = 0 To rst.RecordCount -1
strIPList = strIPList & rst![IpAddress] & ","
Next lngRecCount

strIPList = Left(strIPList, Len(strIPList) -1) ' Takes off the last comma

' Now write it to a text file

geebee said:
the date from excel is sotred as one IP address pr row. the data in access
is stored one IP address per table row.
Click to expand...
Click to expand...
 
K

Klatuu

Check your references. In the VB Editor, Tools-->References. You should have
a reference to the Access Object Library and the DAO library. It may be one
of those missing. I have not seen this problem before. What happens if you
just type it in?

geebee said:
thanks. i am having one problem though: when I type

Dim dbf As Database

the "database" option does not how up. however, the "dataaccesspage" option
does show up.

how can i get "database" to show up?

thanks in advance,
-geebee

Klatuu said:
Hopefully, you can do some VBA coding. This is what it will take. You will
need to create a recordset that contains the IP addresses you want to put
together, then you will need to write that out to a text file, but use the
extension of csv. This will cause Excel to see each IP in a different cell
on the same row.

Dim dbf as Database
Dim rst as Recordset
Dim strIPList as String
Dim lngRecCount as Long

set dbf = CurrentDb
set rst = dbf.MyTableName
If rst.RecordCount = 0 Then
MsgBox "No Data to Process"
GoTo MyExit
Else
rst.MoveLast
rst.MoveFirst
End If

For lngRecCount = 0 To rst.RecordCount -1
strIPList = strIPList & rst![IpAddress] & ","
Next lngRecCount

strIPList = Left(strIPList, Len(strIPList) -1) ' Takes off the last comma

' Now write it to a text file

geebee said:
the date from excel is sotred as one IP address pr row. the data in access
is stored one IP address per table row.


:

Is the data from Excel coming in on one line, or one line per IP?
How is it stored in your table?


:

hi,

i was not sure what forum to pot this on, so i figure this would be a good
start, even for redirection pusposes. Users should be able to upload excel
data into a table. then this table information should be exported to a .csv
file, to get something like:

67.108.115.178,199.169.192.157,66.109.83.6,66.109.83.7

where te different ip addresses are separated by a comma. i tried doing
this, but i am getting each of the IP addresses on different lines of a .txt
fle, like this:

212.138.47.11
135.196.231.255
65.217.232.2
129.33.119.25
68.143.150.50
65.218.126.98
162.58.82.244
161.215.18.51
201.147.134.225
209.208.169.98

does anyone know how i can avoid getting eah IP adress on a different line,
and instead get them on one line, etc, or separated by a comma instead of by
a line?

thanks in advance,
geebee
Click to expand...
Click to expand...
Click to expand...
 
G

geebee

thanks. i made sure te DAO library was enabled and that solved the problem
with "database" not showing up. now i have anotherproblem. the folling line
of te code:

set rst = dbf.MyTableName

results in a "method ordata member not found" error message. why is this?

thanks in advance,
-geebee


Klatuu said:
Check your references. In the VB Editor, Tools-->References. You should have
a reference to the Access Object Library and the DAO library. It may be one
of those missing. I have not seen this problem before. What happens if you
just type it in?

geebee said:
thanks. i am having one problem though: when I type

Dim dbf As Database

the "database" option does not how up. however, the "dataaccesspage" option
does show up.

how can i get "database" to show up?

thanks in advance,
-geebee

Klatuu said:
Hopefully, you can do some VBA coding. This is what it will take. You will
need to create a recordset that contains the IP addresses you want to put
together, then you will need to write that out to a text file, but use the
extension of csv. This will cause Excel to see each IP in a different cell
on the same row.

Dim dbf as Database
Dim rst as Recordset
Dim strIPList as String
Dim lngRecCount as Long

set dbf = CurrentDb
set rst = dbf.MyTableName
If rst.RecordCount = 0 Then
MsgBox "No Data to Process"
GoTo MyExit
Else
rst.MoveLast
rst.MoveFirst
End If

For lngRecCount = 0 To rst.RecordCount -1
strIPList = strIPList & rst![IpAddress] & ","
Next lngRecCount

strIPList = Left(strIPList, Len(strIPList) -1) ' Takes off the last comma

' Now write it to a text file

:

the date from excel is sotred as one IP address pr row. the data in access
is stored one IP address per table row.


:

Is the data from Excel coming in on one line, or one line per IP?
How is it stored in your table?


:

hi,

i was not sure what forum to pot this on, so i figure this would be a good
start, even for redirection pusposes. Users should be able to upload excel
data into a table. then this table information should be exported to a .csv
file, to get something like:

67.108.115.178,199.169.192.157,66.109.83.6,66.109.83.7

where te different ip addresses are separated by a comma. i tried doing
this, but i am getting each of the IP addresses on different lines of a .txt
fle, like this:

212.138.47.11
135.196.231.255
65.217.232.2
129.33.119.25
68.143.150.50
65.218.126.98
162.58.82.244
161.215.18.51
201.147.134.225
209.208.169.98

does anyone know how i can avoid getting eah IP adress on a different line,
and instead get them on one line, etc, or separated by a comma instead of by
a line?

thanks in advance,
geebee
Click to expand...
Click to expand...
Click to expand...
 
J

John Vinson

set rst = dbf.MyTableName

results in a "method ordata member not found" error message. why is this?
Click to expand...

Should be

Set rst = dbf.OpenRecordset("MyTableName")

using of course your own table's actual name.

John W. Vinson[MVP]
 
G

geebee

Another problem. Im sorry if i seem like a spoon-fed aby, but the following
line of code:

Set rst = dbf.OpenRecordset("sheet1")

Is causing a runtime error 13 error message.

why?

thanks in advance,
geebee
 
J

John Vinson

Another problem. Im sorry if i seem like a spoon-fed aby, but the following
line of code:

Set rst = dbf.OpenRecordset("sheet1")

Is causing a runtime error 13 error message.

why?
Click to expand...

Probably because there is no Table named sheet1 in your database.

Check the VBA help for OpenRecordset - you probably also need to
specify the recordset type, e.g.

dbf.OpenRecordset("sheet1", dbOpenSnapshot)

John W. Vinson[MVP]
 
Top