open excel thru access and then change column format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i can open an excel book using vb code in access.
i can also identify the column that i need to change from numeric to text.

however, the code i use to try change to text does not work. what should be
used?

here is what i am using

Set xlapp = CreateObject("excel.application")
Set xlbookformatted = xlapp.workbooks.Open("g:\aaa_rmsco\fee schedule
updates\facility.xls", 3)
xlapp.Visible = False
xlapp.Columns("A:A").Select
xlapp.selection.numberformat = "@"

xlbookformatted.Save
xlbookformatted.Close

i am changing a 61,000 record workbook. so i do not know if there is also a
processing issue because it looks as though nothing is happening when i start
the job
thanks for your help
 
Hi jnewl,
your code is correct. What are you expecting to happen?
After you execute your code the column A is not formatted as text?
 
i cancelled after 20 seconds because thought was not doing anything. the user
sent me an alphanumeric field described as numeric, so when i try to import
the sheet into an access table, i get conversion errors. can not have
because have to do a lot of updates further downstream.
if i manually change the column to text, i have a good transfer.
 
jnewl said:
i can open an excel book using vb code in access.
i can also identify the column that i need to change from numeric to text.

however, the code i use to try change to text does not work. what should
be
used?

here is what i am using

Set xlapp = CreateObject("excel.application")
Set xlbookformatted = xlapp.workbooks.Open("g:\aaa_rmsco\fee schedule
updates\facility.xls", 3)
xlapp.Visible = False
xlapp.Columns("A:A").Select
xlapp.selection.numberformat = "@"

xlbookformatted.Save
xlbookformatted.Close
Click to expand...

Off the top of my head I'd say that you need to create a worksheet object
and set the one you're working on as the current sheet.

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim objRange As Excel.Range

Set objXL = New Excel.Application
Set objWkb = objXL.Workbooks.Open("X:\MyPath")
objXL.Visible = False

Set objSht = objWkb.Worksheets("MySheetName")
objSht.Activate

Set objRange = objSht.Range("A1").CurrentRegion
With objRange 'Format the font
.Select
.Font.Name = "arial"
.Font.Size = 8
.ColumnWidth = 10
End With

Set objRange = Nothing
etc

You could always set the visible property to True and setp through the code
whilst watching what it's doing.

Regards,
Keith.
www.keithwilby.com
 
A couple of possibilities....First, are you formatting before you write the
data ? In that case, write all your data, then try formatting as text.
Second, when you write the data, preceed it with a single quote '. That is
the text identifier and forces the cell to format.

BrerGoose
 
tried this approach but keep getting the error message that trying to format
multi columns. keyed the code as you have it. how do i fix so just getting
col a?

thanks for your help
 
Set xlapp = CreateObject("excel.application")
Set xlbookformatted = xlapp.workbooks.Open("g:\aaa_rmsco\fee schedule
updates\facility.xls", 3)
With xlapp
.Visible = False
.xlbookformatted.Columns("A:A").numberformat = "@"
.xlbookformatted.Save
.xlbookformatted.Close
.Close
.Visible = True
End With
set xlapp = Nothing
set xlbookformatted = Nothing
 
hi, i am getting an error message on .xlbookformatted.columns as not
supported.

i am using access 2003. also, i have found that texttocolumn fixes the field
as text.
when i did a right mouse, manually on col a, and did format cells and chose
text, still got the conversion errors. when i did text to column, import was
fine
 
Sorry I missed it, but the problem is it needs to be referenced at the
worksheet level. Also, you don't have to use (A:A), you can use just (A)

Set xlapp = CreateObject("excel.application")
Set xlbookformatted = xlapp.workbooks.Open("g:\aaa_rmsco\fee schedule
updates\facility.xls", 3)
Set xlSheet = xlbookformatted.Worksheets("SheetName")
With xlapp
.Visible = False
.xlbookformatted.xlSheet.Columns("A").numberformat = "@"
.xlbookformatted.Save
.xlbookformatted.Close
.Close
.Visible = True
End With
set xlapp = Nothing
set xlbookformatted = Nothing
 
hi, thanks for your help. but still get the not supported message
code:
Dim xlapp As Object
Dim xlbookformatted As Object
Dim xlsheet As Object


Set xlapp = CreateObject("excel.application")
Set xlbookformatted = xlapp.workbooks.Open("g:\aaa_rmsco\fee schedule
updates\facility.xls", 3)
Set xlsheet = xlbookformatted.worksheets("rate sheet")
With xlapp
.Visible = False
.xlbookformatted.xlsheet.Columns("A").numberformat = "@"
.xlbookformatted.Save
.xlbookformatted.Close
.Close
.Visible = True
End With

Set xlapp = Nothing
Set xlbookformatted = Nothing


get the message - run time error 438 object does not support this
property or method. should use a different dim statement for xlsheet?
 
ok, i guess i do not know what that code should be.

i ran an excel macro to get the code for changing to text by format cells.
that code was the numberformat = "@". when i tried to import the excel book,
still got conversion errors.

when i did a text to column, it would import properly.

what is code syntax for text to column? i do not have any documentation that
tells me that.

thanks for all your help
 
Open your VBA editor. Select Tools, References.
You will get a dialog box with a list of libraries you can include. Look
for Microsoft Excel Object Libaray. Check the box next to it.
The code should then work.
 
thanks

Klatuu said:
Open your VBA editor. Select Tools, References.
You will get a dialog box with a list of libraries you can include. Look
for Microsoft Excel Object Libaray. Check the box next to it.
The code should then work.
Click to expand...
 
it was already checked. still had the error. what is the syntax for text to
column within with?

thanks
 
jnewl said:
tried this approach but keep getting the error message that trying to
format
multi columns. keyed the code as you have it. how do i fix so just
getting
col a?

thanks for your help
Click to expand...

Set the range object to "A:A".
 
Back
Top