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
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