Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Help With Input Box
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 14
Default Help With Input Box
Hello all,
I'm attempting to update a macro that aparently in the past captured this user's ID and I can't get it edited out. I've searched for LOYONNG, but cannot find it anywhere in the code. The closest I've come is code that concatenates the path and file name in an input box and saves the file to C:\mydocuments\LOYONNG.PMI. I thought I may need to release the variable, but after reading that it's not necessary I got confused and thought I should asl the experts.

When I roll the file out, I would like to have the input box blank.

Any assistance is greatly appreciated.

Here's the part of the code I'm working with.

Sub SaveAllSheets()
Dim Counter2 As Integer
Dim SelSheet As Variant
Dim SelHdrSheet As String
Dim ExportFileName As String
Dim ExportFile As Integer
Dim SaveStatusBar As Boolean
Dim FileNameExists As Boolean
Dim OldStatBar As String
Dim NvsError As String
Dim TotalRows As Long
Dim TotalJrnlHdr As Long
Dim TotalJrnlLn As Long

GetSheet
Application.ScreenUpdating = False
SaveStatusBar = Application.DisplayStatusBar
OldStatBar = Application.StatusBar
NvsError = ""
SheetsExist = 0
ListArray SheetsExist
If SheetsExist = 1 Then
Counter2 = 1
GetImportOptions

'Workbooks(CurWrkBook).Unprotect
'Prompt the user for the file name
Workbooks(MacroBook).DialogSheets("dFileName").Dia logFrame.Caption = GetMsg(Language, 41, 1)
Workbooks(MacroBook).DialogSheets("dFileName").Lab els(1).Text = GetMsg(Language, 41, 2)
Workbooks(MacroBook).DialogSheets("dFileName").Lab els(2).Text = Application.DefaultFilePath
If Workbooks(MacroBook).DialogSheets("dFileName").Edi tBoxes("FileName").Text = "" Then
Workbooks(MacroBook).DialogSheets("dFileName").Edi tBoxes("FileName").Text = Mid(CurWrkBook, 1, InStr(1, CurWrkBook, ".", 1)) & "PMI"
End If
If Workbooks(MacroBook).DialogSheets("dFileName").Sho w Then
Application.ScreenUpdating = False
If Right(Workbooks(MacroBook).DialogSheets("dFileName ").EditBoxes("FileName").Text, 1) < "\" Then
ExportFileName = Application.DefaultFilePath & "\" & Workbooks(MacroBook).DialogSheets("dFileName").Edi tBoxes("FileName").Text
Else
ExportFileName = Application.DefaultFilePath & Workbooks(MacroBook).DialogSheets("dFileName").Edi tBoxes("FileName").Text
End If
If Workbooks(MacroBook).DialogSheets("dFileName").Opt ionButtons("DelimTab").Value = xlOn Then
ExportDelim = Chr(9)
Else
ExportDelim = Workbooks(MacroBook).DialogSheets("dFileName").Edi tBoxes("Delimiter").Text
End If
Else
GoTo EndOfSaveAllSheets
End If

If ExportFileName = "" Then
GoTo EndOfSaveAllSheets
End If

FileNameExists = FileExists(ExportFileName)
If FileNameExists Then
' Warning - File already exist
Workbooks(MacroBook).DialogSheets("dYesNo").Dialog Frame.Caption = GetMsg(Language, 41, 1)
Workbooks(MacroBook).DialogSheets("dYesNo").Labels (1).Text = ExportFileName & GetMsg(Language, 41, 3)
Workbooks(MacroBook).DialogSheets("dYesNo").Labels (2).Text = GetMsg(Language, 41, 4)
If Workbooks(MacroBook).DialogSheets("dYesNo").Show Then
Kill ExportFileName
Else
GoTo EndOfSaveAllSheets
End If
End If

Application.DisplayStatusBar = True
Application.StatusBar = GetMsg(Language, 1, 2)

ExportFile = FreeFile()
Open ExportFileName For Output As ExportFile

Print #ExportFile, "\ID\"
Print #ExportFile, "GL,JOURNAL"
Print #ExportFile, "\DELIMITER\"
Print #ExportFile, ExportDelim

TotalJrnlHdr = 0
TotalJrnlLn = 0

For Each SelSheet In SheetList
SelSheet = SheetList(Counter2)

TotalRows = 0
SelHdrSheet = SelSheet & "_H"
Workbooks(CurWrkBook).Worksheets(SelSheet).Activat e
CheckHdrs
Workbooks(CurWrkBook).Worksheets(SelHdrSheet).Acti vate
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True
ActiveSheet.Cells(3, 1).Value = "%,OA"
NvsError = nVsExportSheet(Workbooks(CurWrkBook).Worksheets(Se lHdrSheet), ExportFile, False, ExportDelim, TotalRows)
TotalJrnlHdr = TotalJrnlHdr + TotalRows
IUHeaderDelete
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True

TotalRows = 0
Workbooks(CurWrkBook).Worksheets(SelSheet).Activat e
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True
FirstJLine = Workbooks(CurWrkBook).Worksheets(SelSheet).Range(" InsertLine").Row
FirstJLine = FirstJLine + 1
ActiveSheet.Cells(FirstJLine, 1).Value = "%,OA"
FormatJournal (SelSheet)
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True
NvsError = nVsExportSheet(Workbooks(CurWrkBook).Worksheets(Se lSheet), ExportFile, True, ExportDelim, TotalRows)
TotalJrnlLn = TotalJrnlLn + TotalRows

Counter2 = Counter2 + 1
Next

Close #ExportFile

' Total of N journals and M lines saved successfully
Title = GetMsg(Language, 96, 1)
Message = GetMsg(Language, 96, 3) & CInt(TotalJrnlHdr) & GetMsg(Language, 96, 4) & CInt(TotalJrnlLn) & GetMsg(Language, 96, 5)
ReturnCd = MsgBox(Message, 64, Title)
Else
'Msg: "No journal entry sheets exist for import."
Title = GetMsg(Language, 96, 1)
Message = GetMsg(Language, 96, 2)
ReturnCd = MsgBox(Message, 64, Title)
End If

EndOfSaveAllSheets:
Workbooks(CurWrkBook).Worksheets(Control).Activate
Application.StatusBar = GetMsg(Language, 1, 3)
'Application.StatusBar = OldStatBar
Application.DisplayStatusBar = SaveStatusBar
End Sub
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 208
Default Help With Input Box
Hi
You are going to have to make a much better job of asking the question
than this! Absolutely no idea what you want.
regards
Paul

On Feb 23, 5:41*pm, Ron wrote:
Hello all,
I'm attempting to update a macro that aparently in the past captured this user's ID and I can't get it edited out. *I've searched for LOYONNG, but cannot find it anywhere in the code. *The closest I've come is code that concatenates the path and file name in an input box and saves the file to C:\mydocuments\LOYONNG.PMI. *I thought I may need to release the variable, but after reading that it's not necessary I got confused and thought I should asl the experts.

When I roll the file out, I would like to have the input box blank.

Any assistance is greatly appreciated. *

Here's the part of the code I'm working with.

Sub SaveAllSheets()
Dim Counter2 As Integer
Dim SelSheet As Variant
Dim SelHdrSheet As String
Dim ExportFileName As String
Dim ExportFile As Integer
Dim SaveStatusBar As Boolean
Dim FileNameExists As Boolean
Dim OldStatBar As String
Dim NvsError As String
Dim TotalRows As Long
Dim TotalJrnlHdr As Long
Dim TotalJrnlLn As Long

* * GetSheet
* * Application.ScreenUpdating = False
* * SaveStatusBar = Application.DisplayStatusBar
* * OldStatBar = Application.StatusBar
* * NvsError = ""
* * SheetsExist = 0
* * ListArray SheetsExist
* * If SheetsExist = 1 Then
* * * * Counter2 = 1
* * * * GetImportOptions

* * * * 'Workbooks(CurWrkBook).Unprotect
* * * * 'Prompt the user for the file name
* * * * Workbooks(MacroBook).DialogSheets("dFileName").Dia logFrame.Caption = GetMsg(Language, 41, 1)
* * * * Workbooks(MacroBook).DialogSheets("dFileName").Lab els(1).Text = GetMsg(Language, 41, 2)
* * * * Workbooks(MacroBook).DialogSheets("dFileName").Lab els(2).Text = Application.DefaultFilePath
* * * * If Workbooks(MacroBook).DialogSheets("dFileName").Edi tBoxes("FileName").Text = "" Then
* * * * * * Workbooks(MacroBook).DialogSheets("dFileName").Edi tBoxes("FileName").Text = Mid(CurWrkBook, 1, InStr(1, CurWrkBook, ".", 1)) & "PMI"
* * * * End If
* * * * If Workbooks(MacroBook).DialogSheets("dFileName").Sho w Then
* * * * * * Application.ScreenUpdating = False
* * * * * * If Right(Workbooks(MacroBook).DialogSheets("dFileName ").EditBoxes("FileName").Text, 1) < "\" Then
* * * * * * * * ExportFileName = Application.DefaultFilePath & "\" & Workbooks(MacroBook).DialogSheets("dFileName").Edi tBoxes("FileName").Text
* * * * * * Else
* * * * * * * * ExportFileName = Application.DefaultFilePath & Workbooks(MacroBook).DialogSheets("dFileName").Edi tBoxes("FileName").Text
* * * * * * End If
* * * * * * If Workbooks(MacroBook).DialogSheets("dFileName")..Op tionButtons("DelimTab").Value = xlOn Then
* * * * * * * * ExportDelim = Chr(9)
* * * * * * Else
* * * * * * * * ExportDelim = Workbooks(MacroBook).DialogSheets("dFileName").Edi tBoxes("Delimiter").Text
* * * * * * End If
* * * * Else
* * * * * * GoTo EndOfSaveAllSheets
* * * * End If

* * * * If ExportFileName = "" Then
* * * * * * GoTo EndOfSaveAllSheets
* * * * End If

* * * * FileNameExists = FileExists(ExportFileName)
* * * * If FileNameExists Then
* * * * * * ' Warning - File already exist
* * * * * * Workbooks(MacroBook).DialogSheets("dYesNo").Dialog Frame.Caption = GetMsg(Language, 41, 1)
* * * * * * Workbooks(MacroBook).DialogSheets("dYesNo").Labels (1).Text = ExportFileName & GetMsg(Language, 41, 3)
* * * * * * Workbooks(MacroBook).DialogSheets("dYesNo").Labels (2).Text = GetMsg(Language, 41, 4)
* * * * * * If Workbooks(MacroBook).DialogSheets("dYesNo").Show Then
* * * * * * * * Kill ExportFileName
* * * * * * Else
* * * * * * * * GoTo EndOfSaveAllSheets
* * * * * * End If
* * * * End If

* * * * Application.DisplayStatusBar = True
* * * * Application.StatusBar = GetMsg(Language, 1, 2)

* * * * ExportFile = FreeFile()
* * * * Open ExportFileName For Output As ExportFile

* * * * Print #ExportFile, "\ID\"
* * * * Print #ExportFile, "GL,JOURNAL"
* * * * Print #ExportFile, "\DELIMITER\"
* * * * Print #ExportFile, ExportDelim

* * * * TotalJrnlHdr = 0
* * * * TotalJrnlLn = 0

* * * * For Each SelSheet In SheetList
* * * * * * SelSheet = SheetList(Counter2)

* * * * * * TotalRows = 0
* * * * * * SelHdrSheet = SelSheet & "_H"
* * * * * * Workbooks(CurWrkBook).Worksheets(SelSheet).Activat e
* * * * * * CheckHdrs
* * * * * * Workbooks(CurWrkBook).Worksheets(SelHdrSheet).Acti vate
* * * * * * ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
* * * * , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
* * * * AllowFormattingRows:=True
* * * * * * ActiveSheet.Cells(3, 1).Value = "%,OA"
* * * * * * NvsError = nVsExportSheet(Workbooks(CurWrkBook)..Worksheets(S elHdrSheet), ExportFile, False, ExportDelim, TotalRows)
* * * * * * TotalJrnlHdr = TotalJrnlHdr + TotalRows
* * * * * * IUHeaderDelete
* * * * * * ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
* * * * , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
* * * * AllowFormattingRows:=True

* * * * * * TotalRows = 0
* * * * * * Workbooks(CurWrkBook).Worksheets(SelSheet).Activat e
* * * * * * ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
* * * * , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
* * * * AllowFormattingRows:=True
* * * * * * FirstJLine = Workbooks(CurWrkBook).Worksheets(SelSheet).Range(" InsertLine").Row
* * * * * * FirstJLine = FirstJLine + 1
* * * * * * ActiveSheet.Cells(FirstJLine, 1).Value = "%,OA"
* * * * * * FormatJournal (SelSheet)
* * * * * * ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
* * * * , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
* * * * AllowFormattingRows:=True
* * * * * * NvsError = nVsExportSheet(Workbooks(CurWrkBook)..Worksheets(S elSheet), ExportFile, True, ExportDelim, TotalRows)
* * * * * * TotalJrnlLn = TotalJrnlLn + TotalRows

* * * * * * Counter2 = Counter2 + 1
* * * * Next

* * * * Close #ExportFile

* * * * ' Total of N journals and M lines saved successfully
* * * * Title = GetMsg(Language, 96, 1)
* * * * Message = GetMsg(Language, 96, 3) & CInt(TotalJrnlHdr) & GetMsg(Language, 96, 4) & CInt(TotalJrnlLn) & GetMsg(Language, 96, 5)
* * * * ReturnCd = MsgBox(Message, 64, Title)
* * Else
* * * * 'Msg: "No journal entry sheets exist for import."
* * * * Title = GetMsg(Language, 96, 1)
* * * * Message = GetMsg(Language, 96, 2)
* * * * ReturnCd = MsgBox(Message, 64, Title)
* * End If

EndOfSaveAllSheets:
* * Workbooks(CurWrkBook).Worksheets(Control).Activate
* * Application.StatusBar = GetMsg(Language, 1, 3)
* * 'Application.StatusBar = OldStatBar
* * Application.DisplayStatusBar = SaveStatusBar
End Sub

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default Help With Input Box
Look at 'Application.DefaultFilePath' in your code above. I bet it's
getting LOYONNG from there, perhaps from the way XL was installed on
that machine?

On Feb 23, 12:41*pm, Ron wrote:
Hello all,
I'm attempting to update a macro that aparently in the past captured this user's ID and I can't get it edited out. *I've searched for LOYONNG, but cannot find it anywhere in the code. *The closest I've come is code that concatenates the path and file name in an input box and saves the file to C:\mydocuments\LOYONNG.PMI. *I thought I may need to release the variable, but after reading that it's not necessary I got confused and thought I should asl the experts.

When I roll the file out, I would like to have the input box blank.

Any assistance is greatly appreciated. *

Here's the part of the code I'm working with.

Sub SaveAllSheets()
Dim Counter2 As Integer
Dim SelSheet As Variant
Dim SelHdrSheet As String
Dim ExportFileName As String
Dim ExportFile As Integer
Dim SaveStatusBar As Boolean
Dim FileNameExists As Boolean
Dim OldStatBar As String
Dim NvsError As String
Dim TotalRows As Long
Dim TotalJrnlHdr As Long
Dim TotalJrnlLn As Long

* * GetSheet
* * Application.ScreenUpdating = False
* * SaveStatusBar = Application.DisplayStatusBar
* * OldStatBar = Application.StatusBar
* * NvsError = ""
* * SheetsExist = 0
* * ListArray SheetsExist
* * If SheetsExist = 1 Then
* * * * Counter2 = 1
* * * * GetImportOptions

* * * * 'Workbooks(CurWrkBook).Unprotect
* * * * 'Prompt the user for the file name
* * * * Workbooks(MacroBook).DialogSheets("dFileName").Dia logFrame.Caption = GetMsg(Language, 41, 1)
* * * * Workbooks(MacroBook).DialogSheets("dFileName").Lab els(1).Text = GetMsg(Language, 41, 2)
* * * * Workbooks(MacroBook).DialogSheets("dFileName").Lab els(2).Text = Application.DefaultFilePath
* * * * If Workbooks(MacroBook).DialogSheets("dFileName").Edi tBoxes("FileName").Text = "" Then
* * * * * * Workbooks(MacroBook).DialogSheets("dFileName").Edi tBoxes("FileName").Text = Mid(CurWrkBook, 1, InStr(1, CurWrkBook, ".", 1)) & "PMI"
* * * * End If
* * * * If Workbooks(MacroBook).DialogSheets("dFileName").Sho w Then
* * * * * * Application.ScreenUpdating = False
* * * * * * If Right(Workbooks(MacroBook).DialogSheets("dFileName ").EditBoxes("FileName").*Text, 1) < "\" Then
* * * * * * * * ExportFileName = Application.DefaultFilePath & "\" & Workbooks(MacroBook).DialogSheets("dFileName").Edi tBoxes("FileName").Text
* * * * * * Else
* * * * * * * * ExportFileName = Application.DefaultFilePath & Workbooks(MacroBook).DialogSheets("dFileName").Edi tBoxes("FileName").Text
* * * * * * End If
* * * * * * If Workbooks(MacroBook).DialogSheets("dFileName")..Op tionButtons("DelimTab").Va*lue = xlOn Then
* * * * * * * * ExportDelim = Chr(9)
* * * * * * Else
* * * * * * * * ExportDelim = Workbooks(MacroBook).DialogSheets("dFileName").Edi tBoxes("Delimiter").Text
* * * * * * End If
* * * * Else
* * * * * * GoTo EndOfSaveAllSheets
* * * * End If

* * * * If ExportFileName = "" Then
* * * * * * GoTo EndOfSaveAllSheets
* * * * End If

* * * * FileNameExists = FileExists(ExportFileName)
* * * * If FileNameExists Then
* * * * * * ' Warning - File already exist
* * * * * * Workbooks(MacroBook).DialogSheets("dYesNo").Dialog Frame.Caption = GetMsg(Language, 41, 1)
* * * * * * Workbooks(MacroBook).DialogSheets("dYesNo").Labels (1).Text = ExportFileName & GetMsg(Language, 41, 3)
* * * * * * Workbooks(MacroBook).DialogSheets("dYesNo").Labels (2).Text = GetMsg(Language, 41, 4)
* * * * * * If Workbooks(MacroBook).DialogSheets("dYesNo").Show Then
* * * * * * * * Kill ExportFileName
* * * * * * Else
* * * * * * * * GoTo EndOfSaveAllSheets
* * * * * * End If
* * * * End If

* * * * Application.DisplayStatusBar = True
* * * * Application.StatusBar = GetMsg(Language, 1, 2)

* * * * ExportFile = FreeFile()
* * * * Open ExportFileName For Output As ExportFile

* * * * Print #ExportFile, "\ID\"
* * * * Print #ExportFile, "GL,JOURNAL"
* * * * Print #ExportFile, "\DELIMITER\"
* * * * Print #ExportFile, ExportDelim

* * * * TotalJrnlHdr = 0
* * * * TotalJrnlLn = 0

* * * * For Each SelSheet In SheetList
* * * * * * SelSheet = SheetList(Counter2)

* * * * * * TotalRows = 0
* * * * * * SelHdrSheet = SelSheet & "_H"
* * * * * * Workbooks(CurWrkBook).Worksheets(SelSheet).Activat e
* * * * * * CheckHdrs
* * * * * * Workbooks(CurWrkBook).Worksheets(SelHdrSheet).Acti vate
* * * * * * ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
* * * * , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
* * * * AllowFormattingRows:=True
* * * * * * ActiveSheet.Cells(3, 1).Value = "%,OA"
* * * * * * NvsError = nVsExportSheet(Workbooks(CurWrkBook)..Worksheets(S elHdrSheet), ExportFile, False, ExportDelim, TotalRows)
* * * * * * TotalJrnlHdr = TotalJrnlHdr + TotalRows
* * * * * * IUHeaderDelete
* * * * * * ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
* * * * , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
* * * * AllowFormattingRows:=True

* * * * * * TotalRows = 0
* * * * * * Workbooks(CurWrkBook).Worksheets(SelSheet).Activat e
* * * * * * ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
* * * * , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
* * * * AllowFormattingRows:=True
* * * * * * FirstJLine = Workbooks(CurWrkBook).Worksheets(SelSheet).Range(" InsertLine").Row
* * * * * * FirstJLine = FirstJLine + 1
* * * * * * ActiveSheet.Cells(FirstJLine, 1).Value = "%,OA"
* * * * * * FormatJournal (SelSheet)
* * * * * * ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
* * * * , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
* * * * AllowFormattingRows:=True
* * * * * * NvsError = nVsExportSheet(Workbooks(CurWrkBook)..Worksheets(S elSheet), ExportFile, True, ExportDelim, TotalRows)
* * * * * * TotalJrnlLn = TotalJrnlLn + TotalRows

* * * * * * Counter2 = Counter2 + 1
* * * * Next

* * * * Close #ExportFile

* * * * ' Total of N journals and M lines saved successfully
* * * * Title = GetMsg(Language, 96, 1)
* * * * Message = GetMsg(Language, 96, 3) & CInt(TotalJrnlHdr) & GetMsg(Language, 96, 4) & CInt(TotalJrnlLn) & GetMsg(Language, 96, 5)
* * * * ReturnCd = MsgBox(Message, 64, Title)
* * Else
* * * * 'Msg: "No journal entry sheets exist for import."
* * * * Title = GetMsg(Language, 96, 1)
* * * * Message = GetMsg(Language, 96, 2)
* * * * ReturnCd = MsgBox(Message, 64, Title)
* * End If

EndOfSaveAllSheets:
* * Workbooks(CurWrkBook).Worksheets(Control).Activate
* * Application.StatusBar = GetMsg(Language, 1, 3)
* * 'Application.StatusBar = OldStatBar
* * Application.DisplayStatusBar = SaveStatusBar
End Sub

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 14
Default Help With Input Box
Thank you, I'll look into that line. Thank you for your response an
understanding the problem. Is there a work around to rid the path of
LOYONNG and capture the user of the current computer?
Thanks, Ron

On Feb 24, 11:21*am, skoalnreds wrote:
Look at *'Application.DefaultFilePath' in your code above. *I bet it's
getting LOYONNG from there, perhaps from the way XL was installed on
that machine?

On Feb 23, 12:41*pm, Ron wrote:

Hello all,
I'm attempting to update a macro that aparently in the past captured this user's ID and I can't get it edited out. *I've searched for LOYONNG, but cannot find it anywhere in the code. *The closest I've come is code that concatenates the path and file name in an input box and saves the file to C:\mydocuments\LOYONNG.PMI. *I thought I may need to release the variable, but after reading that it's not necessary I got confused and thought I should asl the experts.

When I roll the file out, I would like to have the input box blank.

Any assistance is greatly appreciated. *

Here's the part of the code I'm working with.

Sub SaveAllSheets()
Dim Counter2 As Integer
Dim SelSheet As Variant
Dim SelHdrSheet As String
Dim ExportFileName As String
Dim ExportFile As Integer
Dim SaveStatusBar As Boolean
Dim FileNameExists As Boolean
Dim OldStatBar As String
Dim NvsError As String
Dim TotalRows As Long
Dim TotalJrnlHdr As Long
Dim TotalJrnlLn As Long

* * GetSheet
* * Application.ScreenUpdating = False
* * SaveStatusBar = Application.DisplayStatusBar
* * OldStatBar = Application.StatusBar
* * NvsError = ""
* * SheetsExist = 0
* * ListArray SheetsExist
* * If SheetsExist = 1 Then
* * * * Counter2 = 1
* * * * GetImportOptions

* * * * 'Workbooks(CurWrkBook).Unprotect
* * * * 'Prompt the user for the file name
* * * * Workbooks(MacroBook).DialogSheets("dFileName").Dia logFrame.Caption = GetMsg(Language, 41, 1)
* * * * Workbooks(MacroBook).DialogSheets("dFileName").Lab els(1).Text = GetMsg(Language, 41, 2)
* * * * Workbooks(MacroBook).DialogSheets("dFileName").Lab els(2).Text = Application.DefaultFilePath
* * * * If Workbooks(MacroBook).DialogSheets("dFileName").Edi tBoxes("FileName").Text = "" Then
* * * * * * Workbooks(MacroBook).DialogSheets("dFileName").Edi tBoxes("FileName").Text = Mid(CurWrkBook, 1, InStr(1, CurWrkBook, ".", 1)) & "PMI"
* * * * End If
* * * * If Workbooks(MacroBook).DialogSheets("dFileName").Sho w Then
* * * * * * Application.ScreenUpdating = False
* * * * * * If Right(Workbooks(MacroBook).DialogSheets("dFileName ").EditBoxes("FileName").*Text, 1) < "\" Then
* * * * * * * * ExportFileName = Application.DefaultFilePath & "\" & Workbooks(MacroBook).DialogSheets("dFileName").Edi tBoxes("FileName").Text
* * * * * * Else
* * * * * * * * ExportFileName = Application.DefaultFilePath & Workbooks(MacroBook).DialogSheets("dFileName").Edi tBoxes("FileName").Text
* * * * * * End If
* * * * * * If Workbooks(MacroBook).DialogSheets("dFileName").Opt ionButtons("DelimTab").Va*lue = xlOn Then
* * * * * * * * ExportDelim = Chr(9)
* * * * * * Else
* * * * * * * * ExportDelim = Workbooks(MacroBook).DialogSheets("dFileName").Edi tBoxes("Delimiter").Text
* * * * * * End If
* * * * Else
* * * * * * GoTo EndOfSaveAllSheets
* * * * End If

* * * * If ExportFileName = "" Then
* * * * * * GoTo EndOfSaveAllSheets
* * * * End If

* * * * FileNameExists = FileExists(ExportFileName)
* * * * If FileNameExists Then
* * * * * * ' Warning - File already exist
* * * * * * Workbooks(MacroBook).DialogSheets("dYesNo").Dialog Frame.Caption = GetMsg(Language, 41, 1)
* * * * * * Workbooks(MacroBook).DialogSheets("dYesNo").Labels (1).Text = ExportFileName & GetMsg(Language, 41, 3)
* * * * * * Workbooks(MacroBook).DialogSheets("dYesNo").Labels (2).Text = GetMsg(Language, 41, 4)
* * * * * * If Workbooks(MacroBook).DialogSheets("dYesNo").Show Then
* * * * * * * * Kill ExportFileName
* * * * * * Else
* * * * * * * * GoTo EndOfSaveAllSheets
* * * * * * End If
* * * * End If

* * * * Application.DisplayStatusBar = True
* * * * Application.StatusBar = GetMsg(Language, 1, 2)

* * * * ExportFile = FreeFile()
* * * * Open ExportFileName For Output As ExportFile

* * * * Print #ExportFile, "\ID\"
* * * * Print #ExportFile, "GL,JOURNAL"
* * * * Print #ExportFile, "\DELIMITER\"
* * * * Print #ExportFile, ExportDelim

* * * * TotalJrnlHdr = 0
* * * * TotalJrnlLn = 0

* * * * For Each SelSheet In SheetList
* * * * * * SelSheet = SheetList(Counter2)

* * * * * * TotalRows = 0
* * * * * * SelHdrSheet = SelSheet & "_H"
* * * * * * Workbooks(CurWrkBook).Worksheets(SelSheet).Activat e
* * * * * * CheckHdrs
* * * * * * Workbooks(CurWrkBook).Worksheets(SelHdrSheet).Acti vate
* * * * * * ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
* * * * , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
* * * * AllowFormattingRows:=True
* * * * * * ActiveSheet.Cells(3, 1).Value = "%,OA"
* * * * * * NvsError = nVsExportSheet(Workbooks(CurWrkBook).Worksheets(Se lHdrSheet), ExportFile, False, ExportDelim, TotalRows)
* * * * * * TotalJrnlHdr = TotalJrnlHdr + TotalRows
* * * * * * IUHeaderDelete
* * * * * * ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
* * * * , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
* * * * AllowFormattingRows:=True

* * * * * * TotalRows = 0
* * * * * * Workbooks(CurWrkBook).Worksheets(SelSheet).Activat e
* * * * * * ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
* * * * , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
* * * * AllowFormattingRows:=True
* * * * * * FirstJLine = Workbooks(CurWrkBook).Worksheets(SelSheet).Range(" InsertLine").Row
* * * * * * FirstJLine = FirstJLine + 1
* * * * * * ActiveSheet.Cells(FirstJLine, 1).Value = "%,OA"
* * * * * * FormatJournal (SelSheet)
* * * * * * ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
* * * * , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
* * * * AllowFormattingRows:=True
* * * * * * NvsError = nVsExportSheet(Workbooks(CurWrkBook).Worksheets(Se lSheet), ExportFile, True, ExportDelim, TotalRows)
* * * * * * TotalJrnlLn = TotalJrnlLn + TotalRows

* * * * * * Counter2 = Counter2 + 1
* * * * Next

* * * * Close #ExportFile

* * * * ' Total of N journals and M lines saved successfully
* * * * Title = GetMsg(Language, 96, 1)
* * * * Message = GetMsg(Language, 96, 3) & CInt(TotalJrnlHdr) & GetMsg(Language, 96, 4) & CInt(TotalJrnlLn) & GetMsg(Language, 96, 5)
* * * * ReturnCd = MsgBox(Message, 64, Title)
* * Else
* * * * 'Msg: "No journal entry sheets exist for import."
* * * * Title = GetMsg(Language, 96, 1)
* * * * Message = GetMsg(Language, 96, 2)
* * * * ReturnCd = MsgBox(Message, 64, Title)
* * End If

EndOfSaveAllSheets:
* * Workbooks(CurWrkBook).Worksheets(Control).Activate
* * Application.StatusBar = GetMsg(Language, 1, 3)
* * 'Application.StatusBar = OldStatBar
* * Application.DisplayStatusBar = SaveStatusBar
End Sub

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
i want to input data into a range from a simple input location joemassillon Excel Programming 0 March 6th 10 02:17 AM
i want to input data into a range from a simple input location joemassillon Excel Programming 1 March 5th 10 03:37 PM
Cell Color changes On Input even if new input matches default JBark Excel Programming 1 September 25th 09 05:24 AM
input in number form is being multiplied by 1000 when i input. jweinograd Excel Discussion (Misc queries) 4 April 16th 07 11:18 PM
CODE to select range based on User Input or Value of Input Field Sandi Gauthier Excel Programming 4 December 8th 03 03:22 PM


All times are GMT +1. The time now is 11:42 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement