I need help with wrapping text in excel

  • Thread starter Thread starter Al
  • Start date Start date
A

Al

I am exporting data from Access to excel and trying to wrap the text in
excel. Because of the fact that the code lockes selective cells in excel the
user can not format the cells when he gets the file. I would like to be able
to wrap the text in excel. I have a line of code that should do that but it
does not. it gets excuted and still excel does not wrap the text. here is the
code below. any ideas?
thanks



*****************************
Private Sub cmdExport_Click()

Dim strPath As String, wsData As Worksheet, strFile As String, tblName As
String, I As Integer
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWs As Worksheet
Dim xlrng As Range

If IsNull(Me.txtPath) Then
Me.cmdExport.Enabled = True
Me.cmdExport.SetFocus
Me.cmdGo.Enabled = False
MsgBox "Please enter valid path then click <Re-Export>"
Exit Sub
End If

tblName = Me.txtFile
strPath = Me.txtPath
strFile = Replace([txtFile], ":", "")
strFile = Replace(strFile, "/", "")
strFile = Replace(strFile, " ", "_")

DoCmd.TransferSpreadsheet acExport, 8, tblName, strPath & strFile, True

Set xlApp = New Excel.Application
Set xlWb = xlApp.Workbooks.Open(strPath & strFile)
Set xlWs = xlWb.ActiveSheet
Set xlrng = xlWs.Range("A1:Z1")
'Set xlWs = ActiveSheet
'Set xlrng = Range("A1:Z1")

xlApp.Visible = False
xlWs.Columns.AutoFit
'xlWs.Columns(14).Width = xlWs.Columns(12).Width

With xlWs

'lock all cells on worksheet
.Cells.Locked = True

'set column widths
For I = 1 To 13
.Columns(I).ColumnWidth = 17
Next I

For I = 14 To 18
'set column widths
.Columns(I).ColumnWidth = 17
'Unlock Cells
.Columns(I).Cells.Locked = False
Next I
.Columns(16).ColumnWidth = 125

'hide column A
.Columns(1).Hidden = True
' For I = 1 To 1
' .Columns(I).Hidden = True
' Next I
End With

With xlrng
.Font.Bold = True
.WrapText = True
.Borders.LineStyle = xlContinuous
'.Borders.LineStyle = xlMedium
.BorderAround xlContinuous, xlThick, 0
.Locked = True
End With

'protect worksheet
xlWs.Protect UserInterfaceOnly:=True

xlWb.Close savechanges:=True
xlApp.Quit
MsgBox "Data has been exported!"
End Sub
 
Al,
when I want to wrap text in excel, I do it like this:

Set objXLws = objXLApp.ActiveWorkbook.Worksheets(1)

With objXLws.Cells
.WrapText = True
End With


Jeanette Cunningham -- Melbourne Victoria Australia
 
Jeanette,
Thank you very much, this worked. I do not know why your code worked and
mine did not. The only thing I see is that I was using "Range" and you used
"Sheet.Cells". See below. Thank you any way. I do appreciate your help it
saved me a lot of time. It took me some time to work on this:)

Al

Jeanette Cunningham said:
Al,
when I want to wrap text in excel, I do it like this:

Set objXLws = objXLApp.ActiveWorkbook.Worksheets(1)

With objXLws.Cells
.WrapText = True
End With


Jeanette Cunningham -- Melbourne Victoria Australia


Al said:
I am exporting data from Access to excel and trying to wrap the text in
excel. Because of the fact that the code lockes selective cells in excel
the
user can not format the cells when he gets the file. I would like to be
able
to wrap the text in excel. I have a line of code that should do that but
it
does not. it gets excuted and still excel does not wrap the text. here is
the
code below. any ideas?
thanks



*****************************
Private Sub cmdExport_Click()

Dim strPath As String, wsData As Worksheet, strFile As String, tblName As
String, I As Integer
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWs As Worksheet
Dim xlrng As Range

If IsNull(Me.txtPath) Then
Me.cmdExport.Enabled = True
Me.cmdExport.SetFocus
Me.cmdGo.Enabled = False
MsgBox "Please enter valid path then click <Re-Export>"
Exit Sub
End If

tblName = Me.txtFile
strPath = Me.txtPath
strFile = Replace([txtFile], ":", "")
strFile = Replace(strFile, "/", "")
strFile = Replace(strFile, " ", "_")

DoCmd.TransferSpreadsheet acExport, 8, tblName, strPath & strFile, True

Set xlApp = New Excel.Application
Set xlWb = xlApp.Workbooks.Open(strPath & strFile)
Set xlWs = xlWb.ActiveSheet
Set xlrng = xlWs.Range("A1:Z1")
'Set xlWs = ActiveSheet
'Set xlrng = Range("A1:Z1")

xlApp.Visible = False
xlWs.Columns.AutoFit
'xlWs.Columns(14).Width = xlWs.Columns(12).Width

With xlWs

'lock all cells on worksheet
.Cells.Locked = True

'set column widths
For I = 1 To 13
.Columns(I).ColumnWidth = 17
Next I

For I = 14 To 18
'set column widths
.Columns(I).ColumnWidth = 17
'Unlock Cells
.Columns(I).Cells.Locked = False
Next I
.Columns(16).ColumnWidth = 125

'hide column A
.Columns(1).Hidden = True
' For I = 1 To 1
' .Columns(I).Hidden = True
' Next I
End With

With xlrng
.Font.Bold = True
.WrapText = True
.Borders.LineStyle = xlContinuous
'.Borders.LineStyle = xlMedium
.BorderAround xlContinuous, xlThick, 0
.Locked = True
End With

'protect worksheet
xlWs.Protect UserInterfaceOnly:=True

xlWb.Close savechanges:=True
xlApp.Quit
MsgBox "Data has been exported!"
End Sub
Click to expand...
Click to expand...
 
Back
Top