Change VBA Code via Button

[Livvi]

Hello,

I am fairly new to VBA and coding. However I was able to create a macro to copy data from one file into another, save as an excel file as well as an XML file.
While the macro works perfectly fine, I wanted to know if there was any way to create a button to change information in the code itself. For example, the date and the title of the file.
You see, within the code, I have to change the dates to reflect the period of data that I am updating, which should also be reflected in the name of the file. I know I can do a file and replace in the code to change the information to reflect the dates and naming conventions I’d like to use for the updated information.

But is there any way to change the code via a button?

For example, have a command box to change the date, and to rename the file without having to manually change this in the code every time.

[Norie]

Why don't you prompt the user for the required dates/values that need to be changed, store those values in variables and then use the variables in the code?

Or you could enter the dates/values in cells on a worksheet and have the code pull the values from the sheet.

[Livvi]

Why don't you prompt the user for the required dates/values that need to be changed, store those values in variables and then use the variables in the code?

Or you could enter the dates/values in cells on a worksheet and have the code pull the values from the sheet.

Thanks for the response, but how exactly can I do this? Like I said I fairly new to VBA so I'm not exactly sure how to go about doing this.

[Norie]

Can you post the current code and indicate the parts of that need to be changed?

[Livvi]

Can you post the current code and indicate the parts of that need to be changed?

Hello again.

I actually figured out a way to get the date prompt in. I used the below function:

Dim myValue As Variant
myValue = InputBox("Enter First Period Date")
Range("G9:I9").Value = myValue

This is working perfectly, what I am now struggling with is the prompt to open the source file. The code I was trying to use would open the directory/file path but upon opening the file, (which is a csv file) it won't open. See below code:

ChDrive "J:\"
ChDir "J:\TABLES\BAP Loading\Source Files"
fileToOpen = Application _
.GetOpenFilename("All Files , *.*")

I tried changing the .GetOpenFilename("All Files , *.*") to .GetOpenFilename("CSV Files, *.csv") but that won't work. I can't figure out why after I've selected the file it won't open.

Essentially I want a prompt to open the source file, as its going to change every month, as well as a prompt to save the file name I as choose fit, I found the code to prompt the user to enter the date, but I need something that would help open the source file that I want to utilize and a prompt to save the files with the name I wish. As you can see I’m saving the file as an excel in one location and XML in another.

See below.

'
Workbooks.Open Filename:= _
"J:\TABLES\BPA Loading\Source Files\October LOADING FILE.csv"

Workbooks.Open Filename:= _
"J:\TABLES\BPA Loading\BPA Template.xls"
Range("G9:I9").Select
Dim myValue As Variant
myValue = InputBox("Enter First Period Date")
Range("G9:I9").Value = myValue
Sheets("Messages").Select
Windows("LOADING FILE.csv").Activate
ActiveWindow.SmallScroll Down:=-6
Range("A2:V50000").Select
Selection.Copy
Windows("BPA Template.xls").Activate
Range("A10").Select
ActiveSheet.Paste
Sheets("Front Sheet").Select
Application.CutCopyMode = False
ChDir _
"J:\TABLES\BPA Template Excel"
ActiveWorkbook.CheckCompatibility = False
ActiveWorkbook.SaveAs Filename:= _
"J:\TABLES\BPA Loading\BPA Template Excel\October_Loading_P1.xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ChDir _
"J:\TABLES\BPA Loading\Converted XML File"
ActiveWorkbook.SaveAsXMLData Filename:= _
"J:\TABLES\BPA Loading\Converted XML File\October_Loading_P1.xml" _
, Map:=ActiveWorkbook.XmlMaps("FormTR-TFA_Map")
ActiveWindow.Close
Workbooks.Open Filename:= _
"J:\TABLES\BPA Loading\STP Template\BPA Template.xls"
Range("G9:I9").Select
Dim myValue As Variant
myValue = InputBox("Enter Second Period Date")
Range("G9:I9").Value = myValue
Sheets("Messages").Select
Windows("October LOADING FILE.csv").Activate
Range("A50001:V100000").Select
Selection.Copy
Windows("BPA Template.xls").Activate
Range("A10").Select
ActiveSheet.Paste

Sheets("Front Sheet").Select
Application.CutCopyMode = False
ChDir _
"J:\TABLES\BPA Loading\BPA Template Excel"
ActiveWorkbook.CheckCompatibility = False
ActiveWorkbook.SaveAs Filename:= _
"J:\TABLES\ BPA Loading\BPA Template Excel\October_Loading_P2.xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ChDir _
"J:\TABLES\BPA Loading\Converted XML File"
ActiveWorkbook.SaveAsXMLData Filename:= _
"J:\TABLES\BPA Loading\Converted XML File\October_Loading_P2.xml" _
, Map:=ActiveWorkbook.XmlMaps("FormTR-TFA_Map")
ActiveWindow.Close
ActiveWindow.Close
End Sub