Excel macro to prompt for filename

[etradeguru]

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]

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

 

[etradeguru]

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

 

[Gord Dibben]

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

 

[Guest]

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]

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