VBA questions from a NOOB

[poetdi]

Hello! I posted the other day about a macro I was writing, where vendor invoice info is being copied into a report file. I want it to stop at the end of the range of cells being copied, and I'll get back to that, but for now, here are more questions:

I have the .xlsx file, which will have a different name every month. The macro is in the personal macro workbook. It goes to 5 specific cells, copies the data one cell at a time, to the report file, which is created by the macro, which also puts the headings in first and reformats them.

1) Since the file will have a different name every month - how can it be referred to in the code? I recorded the basic macro, and of course it picks up the name of the file I'm using when I switch back and forth during the copy/pastes.

2) The macro creates the new report file each time, and it is referred to as Window(Book 3). Will I not be able to have the macro create the file each time - or should I create the file I'm copying into first - and then what do I call that? The client will probably have to edit the code each month to have the correct file name - they'll hate that.

The question is, I guess, how to refer to file names in a more generic way so they can be re-used without the client having to edit the names?

Thanks in advance for helping a VBA Noo-B.
Diane

 

[BrandonWLH]

Have the user point at the file with a dialog. Just change the file type to match what you want it to look for.

  Dim fn
    fn = Application.GetOpenFilename("Text Files (*.txt;*.csv), *.txt;*.csv") 
    If fn = False Then
        Exit Sub
    Else
       VarForFileName = fn
    'now that you have the name, you can open it or do something else
    End If

 

[Darkspartan]

I am sorry if this doesnt help, but I am a bit confused with what you described.

Whenever I want to save or open something dynamically, I use something like the following...

ThisMonthsFileLocation = "I:\share\Darkspartan\"
Filename = "Report " & Format(date, "mm.dd.yyyy") & ".xls"

ActiveWorkbook.SaveAs Filename:=ThisMonthsFileLocation & Filename, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

 

[BrandonWLH]

The code I posted brings up a Open File dialog and you click on the file, and it stores the name/path of the file in fn. You can then do what ever you want with it.

Dim fn 
fn = Application.GetOpenFilename("Text Files (*.txt;*.csv), *.txt;*.csv") 
If fn = False Then      'no file is picked
    Exit Sub 
End If

ActiveWorkbook.SaveAs Filename:=fn, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

You probably want it to say tho
fn = Application.GetOpenFilename("Excel Files (*.xls), *.xls")

 

[poetdi]

Thanks, guys, I'll give them both a try. I appreciate it!
Diane

 

[BrandonWLH]

What i posted for you Dark wouldnt work for a save as.

For save as I use this function

Function BrowseForFolder(Optional OpenAt As Variant) As Variant
     'Function purpose:  To Browser for a user selected folder.
     'If the "OpenAt" path is provided, open the browser at that directory
     'NOTE:  If invalid, it will open at the Desktop level
     
    Dim ShellApp As Object
     
     'Create a file browser window at the default folder
    Set ShellApp = CreateObject("Shell.Application"). _
    BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
     
     'Set the folder to that selected.  (On error in case cancelled)
    On Error Resume Next
    BrowseForFolder = ShellApp.self.Path
    On Error GoTo 0
     
     'Destroy the Shell Application
    Set ShellApp = Nothing
     
     'Check for invalid or non-entries and send to the Invalid error
     'handler if found
     'Valid selections can begin L: (where L is a letter) or
     '\\ (as in \\servername\sharename.  All others are invalid
    Select Case Mid(BrowseForFolder, 2, 1)
    Case Is = ":"
        If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
    Case Is = "\"
        If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
    Case Else
        GoTo Invalid
    End Select
     
    Exit Function
     
Invalid:
     'If it was determined that the selection was invalid, set to False
    BrowseForFolder = False
     
End Function

and this browses for a folder, and then I use a either a cell with a filename in it or a textbox to get the filename.