Automate text import wizard in a protected sheet
 

Hi,

Rows with old data is protected in the sheet and the new data has to
be entered (in unprotected cells) using text import wizard. For this,
how can I automate text import wizard to run on a protected sheet? I
came up with the following, but its gives an error to run on a
protected sheet.

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 2/4/2008 by cipl9
'
' Keyboard Shortcut: Ctrl+q
'
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False

Selection.TextToColumns Destination:=Range("F2885"),
DataType:=xlDelimited _
, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True,
OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1),
Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1),
Array(10, 1), Array(11, 1), Array(12 _
, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1),
Array(17, 1), Array(18, 1), _
Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1),
Array(23, 1), Array(24, 1)), _
TrailingMinusNumbers:=True

End Sub

I look forward to learning about the solution. Thanking in advance.
SNM

Automate text import wizard in a protected sheet
 

Can you unprotect the sheet, do the work and then reprotect the sheet?

Or maybe you could paste into a temporary (unprotected) worksheet, do the work
there, copy to where it really belongs and then delete that temporary worksheet.

SNM wrote:

Hi,

Rows with old data is protected in the sheet and the new data has to
be entered (in unprotected cells) using text import wizard. For this,
how can I automate text import wizard to run on a protected sheet? I
came up with the following, but its gives an error to run on a
protected sheet.

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 2/4/2008 by cipl9
'
' Keyboard Shortcut: Ctrl+q
'
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False

Selection.TextToColumns Destination:=Range("F2885"),
DataType:=xlDelimited _
, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True,
OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1),
Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1),
Array(10, 1), Array(11, 1), Array(12 _
, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1),
Array(17, 1), Array(18, 1), _
Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1),
Array(23, 1), Array(24, 1)), _
TrailingMinusNumbers:=True

End Sub

I look forward to learning about the solution. Thanking in advance.
SNM

--

Dave Peterson