HELP! I'm stalling out. I created this last year for use in a Word document.
Is that why it's not working in Excel? It bombs on the "SerialNumber =
System.PrivateProfileString("C:\Setting.txt", "MacroSettings",
"SerialNumber")" line. It doesn't go any further than that, so I don't know
if it will work past that, either. If there's anything here that you think
wouldn't work in Excel or any tips on how to troubleshoot this, please get
back to me!!! Thanks again for your brains when mine are lacking!!!!!


Dim Message As String, Title As String, Default As String, NumCopies As Long
Dim Rng1 As Range

'Set prompt
Message = "Enter the number of copies that you want to print"
'Set Title
Title = "Print"
'Set Default
Default = "1"

'Display message, title, and default value
NumCopies = Val(InputBox(Message, Title, Default))
SerialNumber = System.PrivateProfileString("C:\Setting.txt",
"MacroSettings", "SerialNumber")

If SerialNumber = "" Then
SerialNumber = 1
End If

Set Rng1 = ActiveDocument.Bookmarks("SerialNumber").Range
Counter = 0

While Counter < NumCopies
Rng1.Delete
Rng1.Text = Format(SerialNumber, "00#")
ActiveDocument.PrintOut
SerialNumber = SerialNumber + 1
Counter = Counter + 1
Wend

'Save the next number back to the Settings.txt file ready for the next use.
System.PrivateProfileString("C:\Settings.txt", "MacroSettings",
"SerialNumber") = SerialNumber

'Recreate the bookmark ready for the next use.
With ActiveDocument.Bookmarks
.Add Name:="SerialNumber", Range:=Rng1
End With

ActiveDocument.Save


End Function



End Function

You've got all kinds of Word-specific references in there that will
"bomb". For instance, ActiveDocument should be ActiveSheet in Excel.
ActiveSheet does not have a Bookmarks collection. System is not a
member of the Excel.Application class (Word-specific). Also, it looks
like you're using a settings file, and I'm not sure how this feature
is supported - as far as I know you normally deal with the registry,
rather than a settings file, with Excel applications.

It would be better to specify what you want this macro to do, so
someone could port it to Excel for you.


On Oct 30, 2:55 pm, KDG wrote:
HELP! I'm stalling out. I created this last year for use in a Word document.
Is that why it's not working in Excel? It bombs on the "SerialNumber =
System.PrivateProfileString("C:\Setting.txt", "MacroSettings",
"SerialNumber")" line. It doesn't go any further than that, so I don't know
if it will work past that, either. If there's anything here that you think
wouldn't work in Excel or any tips on how to troubleshoot this, please get
back to me!!! Thanks again for your brains when mine are lacking!!!!!

Dim Message As String, Title As String, Default As String, NumCopies As Long
Dim Rng1 As Range

'Set prompt
Message = "Enter the number of copies that you want to print"
'Set Title
Title = "Print"
'Set Default
Default = "1"

'Display message, title, and default value
NumCopies = Val(InputBox(Message, Title, Default))
SerialNumber = System.PrivateProfileString("C:\Setting.txt",
"MacroSettings", "SerialNumber")

If SerialNumber = "" Then
SerialNumber = 1
End If

Set Rng1 = ActiveDocument.Bookmarks("SerialNumber").Range
Counter = 0

While Counter < NumCopies
Rng1.Delete
Rng1.Text = Format(SerialNumber, "00#")
ActiveDocument.PrintOut
SerialNumber = SerialNumber + 1
Counter = Counter + 1
Wend

'Save the next number back to the Settings.txt file ready for the next use.
System.PrivateProfileString("C:\Settings.txt", "MacroSettings",
"SerialNumber") = SerialNumber

'Recreate the bookmark ready for the next use.
With ActiveDocument.Bookmarks
.Add Name:="SerialNumber", Range:=Rng1
End With

ActiveDocument.Save

End Function

End Function

Thanks... I kinda figured that was why it wasn't going to work. I ended up
finding another (simpler) solution from another of the postings and I'm less
crazy now. Thanks so much for the timely response. Us novices really lean on
your advice and expertise.

"iliace" wrote:

You've got all kinds of Word-specific references in there that will
"bomb". For instance, ActiveDocument should be ActiveSheet in Excel.
ActiveSheet does not have a Bookmarks collection. System is not a
member of the Excel.Application class (Word-specific). Also, it looks
like you're using a settings file, and I'm not sure how this feature
is supported - as far as I know you normally deal with the registry,
rather than a settings file, with Excel applications.

It would be better to specify what you want this macro to do, so
someone could port it to Excel for you.


On Oct 30, 2:55 pm, KDG wrote:
HELP! I'm stalling out. I created this last year for use in a Word document.
Is that why it's not working in Excel? It bombs on the "SerialNumber =
System.PrivateProfileString("C:\Setting.txt", "MacroSettings",
"SerialNumber")" line. It doesn't go any further than that, so I don't know
if it will work past that, either. If there's anything here that you think
wouldn't work in Excel or any tips on how to troubleshoot this, please get
back to me!!! Thanks again for your brains when mine are lacking!!!!!

Dim Message As String, Title As String, Default As String, NumCopies As Long
Dim Rng1 As Range

'Set prompt
Message = "Enter the number of copies that you want to print"
'Set Title
Title = "Print"
'Set Default
Default = "1"

'Display message, title, and default value
NumCopies = Val(InputBox(Message, Title, Default))
SerialNumber = System.PrivateProfileString("C:\Setting.txt",
"MacroSettings", "SerialNumber")

If SerialNumber = "" Then
SerialNumber = 1
End If

Set Rng1 = ActiveDocument.Bookmarks("SerialNumber").Range
Counter = 0

While Counter < NumCopies
Rng1.Delete
Rng1.Text = Format(SerialNumber, "00#")
ActiveDocument.PrintOut
SerialNumber = SerialNumber + 1
Counter = Counter + 1
Wend

'Save the next number back to the Settings.txt file ready for the next use.
System.PrivateProfileString("C:\Settings.txt", "MacroSettings",
"SerialNumber") = SerialNumber

'Recreate the bookmark ready for the next use.
With ActiveDocument.Bookmarks
.Add Name:="SerialNumber", Range:=Rng1
End With

ActiveDocument.Save

End Function

End Function