keeping "#" in the column names when exporting to Excel

[GEORGIA]

When I export out a query or table into Excel, column names with "#" turns
into "."..for example:.. Check # will be Check . in excel... how can I keep
"#" once it gets exported into Excel??

Thanks!

 

[Roger Carlson]

I ran into this not too long ago and the only answer I found was to build my
own Export function, building a text file (.csv) with low-level I/O.
Something like this:

Function ExportTable()
Dim dbs As Database, rst As Recordset
Dim Directory As String
Dim MyString As String

Set dbs = CurrentDb
Directory = (Mid(dbs.Name, 1, Len(dbs.Name) - Len(Dir(dbs.Name))))

Open Directory & "\table7.csv" For Output As #1

Set rst = dbs.OpenRecordset("Table7")

Print #1, """ID"",""Check #"""

Do While Not rst.EOF

MyString = rst!id & ",""" & rst![Check #] & """"
Print #1, MyString
rst.MoveNext

Loop

MsgBox "Done!"

' Close text file.
Close #1
rst.Close
Set dbs = Nothing

End Function

This is another good reason for not putting non-alphanumeric characters in
your field names.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[GEORGIA]

Thanks I'll give it try. I actually have more than 1 column with "#", do I
just add it next to the "check #" ?

I ran into this not too long ago and the only answer I found was to build my
own Export function, building a text file (.csv) with low-level I/O.
Something like this:

Function ExportTable()
Dim dbs As Database, rst As Recordset
Dim Directory As String
Dim MyString As String

Set dbs = CurrentDb
Directory = (Mid(dbs.Name, 1, Len(dbs.Name) - Len(Dir(dbs.Name))))

Open Directory & "\table7.csv" For Output As #1

Set rst = dbs.OpenRecordset("Table7")

Print #1, """ID"",""Check #"""

Do While Not rst.EOF

MyString = rst!id & ",""" & rst![Check #] & """"
Print #1, MyString
rst.MoveNext

Loop

MsgBox "Done!"

' Close text file.
Close #1
rst.Close
Set dbs = Nothing

End Function

This is another good reason for not putting non-alphanumeric characters in
your field names.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

When I export out a query or table into Excel, column names with "#" turns
into "."..for example:.. Check # will be Check . in excel... how can I
keep
"#" once it gets exported into Excel??

Thanks!

 

[Roger Carlson]

Yes. You concatenate all of your field names and field values into the
strings. You have to watch the format closely so it will create a true CSV
that Excel can open. To do that, create a true CSV with the export function
and open it in Notepad. Make sure you format your strings exactly the way
they are in the real CSV.

Personally, however, I'd change the name of the field to Check Num or some
such, but I understand that sometimes the names can't be changed.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thanks I'll give it try. I actually have more than 1 column with "#", do
I
just add it next to the "check #" ?

I ran into this not too long ago and the only answer I found was to build
my
own Export function, building a text file (.csv) with low-level I/O.
Something like this:

Function ExportTable()
Dim dbs As Database, rst As Recordset
Dim Directory As String
Dim MyString As String

Set dbs = CurrentDb
Directory = (Mid(dbs.Name, 1, Len(dbs.Name) - Len(Dir(dbs.Name))))

Open Directory & "\table7.csv" For Output As #1

Set rst = dbs.OpenRecordset("Table7")

Print #1, """ID"",""Check #"""

Do While Not rst.EOF

MyString = rst!id & ",""" & rst![Check #] & """"
Print #1, MyString
rst.MoveNext

Loop

MsgBox "Done!"

' Close text file.
Close #1
rst.Close
Set dbs = Nothing

End Function

This is another good reason for not putting non-alphanumeric characters
in
your field names.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

When I export out a query or table into Excel, column names with "#"
turns
into "."..for example:.. Check # will be Check . in excel... how can I
keep
"#" once it gets exported into Excel??

Thanks!

 

[GEORGIA]

Thanks for your help. It worked.
Yes, Unfortunately, I receive the file from the bank, and we needed to have
same format or our interface program won't work..

Again, thanks for your help!

Yes. You concatenate all of your field names and field values into the
strings. You have to watch the format closely so it will create a true CSV
that Excel can open. To do that, create a true CSV with the export function
and open it in Notepad. Make sure you format your strings exactly the way
they are in the real CSV.

Personally, however, I'd change the name of the field to Check Num or some
such, but I understand that sometimes the names can't be changed.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thanks I'll give it try. I actually have more than 1 column with "#", do
I
just add it next to the "check #" ?

I ran into this not too long ago and the only answer I found was to build
my
own Export function, building a text file (.csv) with low-level I/O.
Something like this:

Function ExportTable()
Dim dbs As Database, rst As Recordset
Dim Directory As String
Dim MyString As String

Set dbs = CurrentDb
Directory = (Mid(dbs.Name, 1, Len(dbs.Name) - Len(Dir(dbs.Name))))

Open Directory & "\table7.csv" For Output As #1

Set rst = dbs.OpenRecordset("Table7")

Print #1, """ID"",""Check #"""

Do While Not rst.EOF

MyString = rst!id & ",""" & rst![Check #] & """"
Print #1, MyString
rst.MoveNext

Loop

MsgBox "Done!"

' Close text file.
Close #1
rst.Close
Set dbs = Nothing

End Function

This is another good reason for not putting non-alphanumeric characters
in
your field names.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

When I export out a query or table into Excel, column names with "#"
turns
into "."..for example:.. Check # will be Check . in excel... how can I
keep
"#" once it gets exported into Excel??

Thanks!