Excel macro to prompt for filename
 

I am a relative beginner at macros. I have managed to automate a 10
minute manual cut, paste, format routine into a 10 second macro. (pat
on the back for me!). However, in order to complete the two seperate
parts into one nice process I need to be able to prompt for a file name
to be input. The filename always changes, but it is always in the same
location on the network. Currently the file is hardcoded into the macro
and I have to edit the macro each time to change the filename. So your
help will be greatly appreciated. Thank you in anticipation.

Excel macro to prompt for filename
 

Something like:

Dim s As String
s = Application.InputBox("Enter filename: ", 2)
Workbooks.Open Filename:="C:\Documents and Settings\Owner\My Documents\" & s
--
Gary's Student


" wrote:

I am a relative beginner at macros. I have managed to automate a 10
minute manual cut, paste, format routine into a 10 second macro. (pat
on the back for me!). However, in order to complete the two seperate
parts into one nice process I need to be able to prompt for a file name
to be input. The filename always changes, but it is always in the same
location on the network. Currently the file is hardcoded into the macro
and I have to edit the macro each time to change the filename. So your
help will be greatly appreciated. Thank you in anticipation.

Excel macro to prompt for filename
 

If the folder is on a network drive that is mapped to a letter (like the N:
drive???), then take a look at ChDrive and ChDir in VBA's help.

If you refer to the folder\file by its UNC path
(\\something\somethingelse\filename.xls), you can use an API call. In fact,
this works with mapped drives, too:

Here's a sample, but with getsaveasfilename.

Option Explicit
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
Sub ChDirNet(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub
Sub testme01()

Dim myFileName As Variant
Dim myCurFolder As String
Dim myNewFolder As String
Dim Wkbk as workbook

myCurFolder = CurDir
myNewFolder = "\\share\folder1\folder2"

On Error Resume Next
ChDirNet myNewFolder
If Err.Number < 0 Then
'what should happen
MsgBox "Please change to your own folder"
Err.Clear
End If
On Error GoTo 0

myFileName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")

ChDirNet myCurFolder

If myFileName = False Then
Exit Sub 'user hit cancel
End If

'do your stuff to open it and process it.
Set wkbk = workbooks.open(filename:=myfilename)

'....

End Sub

wrote:

I am a relative beginner at macros. I have managed to automate a 10
minute manual cut, paste, format routine into a 10 second macro. (pat
on the back for me!). However, in order to complete the two seperate
parts into one nice process I need to be able to prompt for a file name
to be input. The filename always changes, but it is always in the same
location on the network. Currently the file is hardcoded into the macro
and I have to edit the macro each time to change the filename. So your
help will be greatly appreciated. Thank you in anticipation.

--

Dave Peterson

Excel macro to prompt for filename
 

For any one who might be interested
I resolved my issue as follows:-

extract = InputBox("Please enter the 4 digit Extract File number")
extractfile = "filepath\" & extract & ".txt"
Workbooks.OpenText (extractfile), DataType:=xlDelimited, Other:=True,
OtherChar:="|"

Regards
Mark
Dave Peterson wrote:
If the folder is on a network drive that is mapped to a letter (like the N:
drive???), then take a look at ChDrive and ChDir in VBA's help.

If you refer to the folder\file by its UNC path
(\\something\somethingelse\filename.xls), you can use an API call. In fact,
this works with mapped drives, too:

Here's a sample, but with getsaveasfilename.

Option Explicit
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
Sub ChDirNet(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub
Sub testme01()

Dim myFileName As Variant
Dim myCurFolder As String
Dim myNewFolder As String
Dim Wkbk as workbook

myCurFolder = CurDir
myNewFolder = "\\share\folder1\folder2"

On Error Resume Next
ChDirNet myNewFolder
If Err.Number < 0 Then
'what should happen
MsgBox "Please change to your own folder"
Err.Clear
End If
On Error GoTo 0

myFileName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")

ChDirNet myCurFolder

If myFileName = False Then
Exit Sub 'user hit cancel
End If

'do your stuff to open it and process it.
Set wkbk = workbooks.open(filename:=myfilename)

'....

End Sub

wrote:

I am a relative beginner at macros. I have managed to automate a 10
minute manual cut, paste, format routine into a 10 second macro. (pat
on the back for me!). However, in order to complete the two seperate
parts into one nice process I need to be able to prompt for a file name
to be input. The filename always changes, but it is always in the same
location on the network. Currently the file is hardcoded into the macro
and I have to edit the macro each time to change the filename. So your
help will be greatly appreciated. Thank you in anticipation.

--

Dave Peterson

Macro that add several worksheets and set the print out to legal s
 

Hello,

How do I change my code so that any worksheets within the workbook opened to
print in legal size? Currently, I have to run my macro on every worksheet so
this does not seem to save me time.

Also, I would like to know how can set a unique command that will
automatically run the macro on any workbook opened without me going to Tool
- macro - then run.

My intent is to have a macro that add new worksheets and set the size of
the print out to landscape legal format.
Please help!

Public Sub AddAWorbook()
Worksheets.Add

End Sub
Public Sub PageSet()

With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.CenterHeader = " "
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed

End With
End Sub

Macro that add several worksheets and set the print out to legal s
 

To set up each worksheet for printing in legal size you could place
Workbook_Open code or Workbook_BeforePrint code in Thisworkbook module.

I would prefer the Workbook_BeforePrint personally but your choice.

Private Sub Workbook_Open()
'or Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet
'Worksheet.Add 'if you want to add a sheet at this point
'Don't run it here if using BeforePrint
For Each ws In ActiveWorkbook.Sheets
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.CenterHeader = " "
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
Next ws
End Sub

Do you mean you really want to add a worksheet to "any" workbook that is opened?

Or just certain workbook(s) that are opened?

Your answer will determine the response and code and where to place that code.


Gord Dibben MS Excel MVP

On Wed, 3 Jan 2007 09:29:01 -0800, Le Jurassien wrote:

Hello,

How do I change my code so that any worksheets within the workbook opened to
print in legal size? Currently, I have to run my macro on every worksheet so
this does not seem to save me time.

Also, I would like to know how can set a unique command that will
automatically run the macro on any workbook opened without me going to Tool
- macro - then run.

My intent is to have a macro that add new worksheets and set the size of
the print out to landscape legal format.
Please help!

Public Sub AddAWorbook()
Worksheets.Add

End Sub
Public Sub PageSet()

With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.CenterHeader = " "
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed

End With
End Sub

Macro that add several worksheets and set the print out to leg
 

Indeed, I want to add new worksheets (like 10 at time) out of reports that I
get in excel format on daily basis then I need to customize the reports. I m
trying to cut down on the number of time I have to "click insert new
worksheet from the excel toolbar." Thus, Having a macro that will simplify
this task would be very helpful.

Thanks,

"Gord Dibben" wrote:

To set up each worksheet for printing in legal size you could place
Workbook_Open code or Workbook_BeforePrint code in Thisworkbook module.

I would prefer the Workbook_BeforePrint personally but your choice.

Private Sub Workbook_Open()
'or Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet
'Worksheet.Add 'if you want to add a sheet at this point
'Don't run it here if using BeforePrint
For Each ws In ActiveWorkbook.Sheets
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.CenterHeader = " "
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
Next ws
End Sub

Do you mean you really want to add a worksheet to "any" workbook that is opened?

Or just certain workbook(s) that are opened?

Your answer will determine the response and code and where to place that code.


Gord Dibben MS Excel MVP

On Wed, 3 Jan 2007 09:29:01 -0800, Le Jurassien wrote:

Hello,

How do I change my code so that any worksheets within the workbook opened to
print in legal size? Currently, I have to run my macro on every worksheet so
this does not seem to save me time.

Also, I would like to know how can set a unique command that will
automatically run the macro on any workbook opened without me going to Tool
- macro - then run.

My intent is to have a macro that add new worksheets and set the size of
the print out to landscape legal format.
Please help!

Public Sub AddAWorbook()
Worksheets.Add

End Sub
Public Sub PageSet()

With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.CenterHeader = " "
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed

End With
End Sub

Macro that add several worksheets and set the print out to leg
 

Sub Add_Sheets()
For i = 10 To 1 Step -1
Worksheets.Add.Name = "Newsheet " & i
Next
End Sub

I suppose the best place to put the macro would be in your Personal.xls so it
available for all open workbooks.


Gord

On Wed, 3 Jan 2007 15:45:01 -0800, Le Jurassien wrote:

Indeed, I want to add new worksheets (like 10 at time) out of reports that I
get in excel format on daily basis then I need to customize the reports. I m
trying to cut down on the number of time I have to "click insert new
worksheet from the excel toolbar." Thus, Having a macro that will simplify
this task would be very helpful.

Thanks,

"Gord Dibben" wrote:

To set up each worksheet for printing in legal size you could place
Workbook_Open code or Workbook_BeforePrint code in Thisworkbook module.

I would prefer the Workbook_BeforePrint personally but your choice.

Private Sub Workbook_Open()
'or Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet
'Worksheet.Add 'if you want to add a sheet at this point
'Don't run it here if using BeforePrint
For Each ws In ActiveWorkbook.Sheets
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.CenterHeader = " "
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
Next ws
End Sub

Do you mean you really want to add a worksheet to "any" workbook that is opened?

Or just certain workbook(s) that are opened?

Your answer will determine the response and code and where to place that code.


Gord Dibben MS Excel MVP

On Wed, 3 Jan 2007 09:29:01 -0800, Le Jurassien wrote:

Hello,

How do I change my code so that any worksheets within the workbook opened to
print in legal size? Currently, I have to run my macro on every worksheet so
this does not seem to save me time.

Also, I would like to know how can set a unique command that will
automatically run the macro on any workbook opened without me going to Tool
- macro - then run.

My intent is to have a macro that add new worksheets and set the size of
the print out to landscape legal format.
Please help!

Public Sub AddAWorbook()
Worksheets.Add

End Sub
Public Sub PageSet()

With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.CenterHeader = " "
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed

End With
End Sub

Macro that add several worksheets and set the print out to leg
 

Thanks, I'll keep you posted upon my completion.

"Gord Dibben" wrote:

Sub Add_Sheets()
For i = 10 To 1 Step -1
Worksheets.Add.Name = "Newsheet " & i
Next
End Sub

I suppose the best place to put the macro would be in your Personal.xls so it
available for all open workbooks.


Gord

On Wed, 3 Jan 2007 15:45:01 -0800, Le Jurassien wrote:

Indeed, I want to add new worksheets (like 10 at time) out of reports that I
get in excel format on daily basis then I need to customize the reports. I m
trying to cut down on the number of time I have to "click insert new
worksheet from the excel toolbar." Thus, Having a macro that will simplify
this task would be very helpful.

Thanks,

"Gord Dibben" wrote:

To set up each worksheet for printing in legal size you could place
Workbook_Open code or Workbook_BeforePrint code in Thisworkbook module.

I would prefer the Workbook_BeforePrint personally but your choice.

Private Sub Workbook_Open()
'or Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet
'Worksheet.Add 'if you want to add a sheet at this point
'Don't run it here if using BeforePrint
For Each ws In ActiveWorkbook.Sheets
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.CenterHeader = " "
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
Next ws
End Sub

Do you mean you really want to add a worksheet to "any" workbook that is opened?

Or just certain workbook(s) that are opened?

Your answer will determine the response and code and where to place that code.


Gord Dibben MS Excel MVP

On Wed, 3 Jan 2007 09:29:01 -0800, Le Jurassien wrote:

Hello,

How do I change my code so that any worksheets within the workbook opened to
print in legal size? Currently, I have to run my macro on every worksheet so
this does not seem to save me time.

Also, I would like to know how can set a unique command that will
automatically run the macro on any workbook opened without me going to Tool
- macro - then run.

My intent is to have a macro that add new worksheets and set the size of
the print out to landscape legal format.
Please help!

Public Sub AddAWorbook()
Worksheets.Add

End Sub
Public Sub PageSet()

With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.CenterHeader = " "
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed

End With
End Sub