A
Al
I am exporting a table from Access to Excel for some one who is going to make
comments in specific columns then I import the excel file back to access with
the comments. The system works OK importing and Exporting however, I am
trying to lock columns A:M and unlock N:R for the Responder to write their
comments. I am also trying to wrap text on all cells. below see the code.
***********************************************
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 Excel.Worksheet
Dim xlrng As Excel.Range
If IsNull(txtPath) Then
Me.cmdExport.Enabled = True
Me.cmdExport.SetFocus
Me.cmdGo.Enabled = False
MsgBox "Please enter valid path then click <Re-Export>"
Exit Sub
Else
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")
xlApp.Visible = False
xlWs.Columns.AutoFit
'xlWs.Columns(14).Width = xlWs.Columns(12).Width
With xlWs
For I = 1 To 1
.Columns(I).Hidden = True
Next I
'Unlock AE Cells
For I = 14 To 18
.Columns(I).ColumnWidth = 17
Next I
.Columns(16).ColumnWidth = 125
.Protect UserInterfaceOnly:=True
.Cells.Locked = False
'Lock all cells that are not AE cells
For I = 1 To 13
.Columns(I).ColumnWidth = 17
Next I
.Columns(16).ColumnWidth = 125
.Protect UserInterfaceOnly:=True
.Cells.Locked = True 'lock all cells on sheet 1-13 (A:M)
End With
With xlrng
.Font.Bold = True
.Borders.LineStyle = xlContinuous
'.Borders.LineStyle = xlMedium
.BorderAround xlContinuous, xlThick, 0
.Locked = True
End With
xlWb.Close savechanges:=True
xlApp.Quit
MsgBox "Data has been exported!"
End If
End Sub
******************************************
Can some one help
thanks
comments in specific columns then I import the excel file back to access with
the comments. The system works OK importing and Exporting however, I am
trying to lock columns A:M and unlock N:R for the Responder to write their
comments. I am also trying to wrap text on all cells. below see the code.
***********************************************
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 Excel.Worksheet
Dim xlrng As Excel.Range
If IsNull(txtPath) Then
Me.cmdExport.Enabled = True
Me.cmdExport.SetFocus
Me.cmdGo.Enabled = False
MsgBox "Please enter valid path then click <Re-Export>"
Exit Sub
Else
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")
xlApp.Visible = False
xlWs.Columns.AutoFit
'xlWs.Columns(14).Width = xlWs.Columns(12).Width
With xlWs
For I = 1 To 1
.Columns(I).Hidden = True
Next I
'Unlock AE Cells
For I = 14 To 18
.Columns(I).ColumnWidth = 17
Next I
.Columns(16).ColumnWidth = 125
.Protect UserInterfaceOnly:=True
.Cells.Locked = False
'Lock all cells that are not AE cells
For I = 1 To 13
.Columns(I).ColumnWidth = 17
Next I
.Columns(16).ColumnWidth = 125
.Protect UserInterfaceOnly:=True
.Cells.Locked = True 'lock all cells on sheet 1-13 (A:M)
End With
With xlrng
.Font.Bold = True
.Borders.LineStyle = xlContinuous
'.Borders.LineStyle = xlMedium
.BorderAround xlContinuous, xlThick, 0
.Locked = True
End With
xlWb.Close savechanges:=True
xlApp.Quit
MsgBox "Data has been exported!"
End If
End Sub
******************************************
Can some one help
thanks