permanently storing variables until I change it

[jordanburch]

Hey guys,

im trying to store variables so that when I click on a button and run a sub it doesnt forget it the next time I run it. The only time I want it to change is when I run the initial sub that requests the input box variables. I also would lik excel to remember these variables when I close and reopen the workbook. How can this be done?

Option Explicit
Global data_wbk1 As String
Global data_wbk2 As String
Global data_wbk3 As String
Global data_wbk4 As String
Global data_wbk5 As String
Global data_wbk6 As String
Global data_wbk7 As String
Global data_wbk8 As String
Global data_wbk9 As String
Global data_wbk10 As String


Public Sub openfiles()

data_wbk2 = InputBox("Enter month I.E. 08-MAY20", Default:="08-MAY20")
data_wbk3 = Right(data_wbk2, 5)
data_wbk1 = "FY" & Right(data_wbk3, 2)
Workbooks.Open ("K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\Monthly Suspense Recon\" & data_wbk1 & "\" & data_wbk2 & "\" & "CARS" & "\" & data_wbk3 & " " & "CARS Detail Transaction Lines.xls*")
data_wbk1 = "FY" & Right(data_wbk3, 2)
data_wbk4 = data_wbk1
data_wbk2 = InputBox("Enter month I.E. 08-MAY20", Default:="08-MAY20")
data_wbk5 = Left(data_wbk2, 2) & " " & "-" & " " & Mid(data_wbk2, 4, 3) & " " & "20" & Right(data_wbk2, 2)
data_wbk6 = InputBox("Enter month Name I.E. YYYYMM:", Default:="202005")

data_wbk7 = Left(data_wbk3, 3)
data_wbk8 = InputBox("Enter Prior month Name I.E. APRIL:", Default:="APRIL")
data_wbk9 = InputBox("Enter Prior month I.E. 07 - APR 2020", Default:="07 - APR 2020")
data_wbk10 = InputBox("Enter month Name I.E. YYYYMM:", Default:="202004")

THanks!

Jordan

 

[Fluff]

You could right the variables to a sheet & then read them back in when you open the workbook.

 

[jordanburch]

You could right the variables to a sheet & then read them back in when you open the workbook.

thanks fluff! I keep searching for that solution. Is there a thread about that? Or how would I do that in general?

Jordan

 

[Fluff]

Why do you need to do it when you are assigning values to the variables anyway?

 

[jordanburch]

Why do you need to do it when you are assigning values to the variables anyway?

because when I rerun the same script it doesnt remember the variables. It just forgets them. Like if I click a button to run a macro and for whatever reason I want to rerun it I delete the sheet it imported and then click rerun and it forgets the variables. As I understand it the only way to do that is putting it inside a spreadsheet. does that make sense?

 

[Tupe77]

"As I understand it the only way to do that is putting it inside a spreadsheet. does that make sense?"

Yes.

This is just to get you started.

Save to workbook->
Thisworkbook.Worksheets("YourSheetName").range("A1").value = data_wbk1

data_wbk? seem to be Strings so it can be stored as is in the cell.


Read from workbook->
data_wbk1 = Thisworkbook.Worksheets("YourSheetName").range("A1").value

 

[jordanburch]

"As I understand it the only way to do that is putting it inside a spreadsheet. does that make sense?"

Yes.

This is just to get you started.

Save to workbook->
Thisworkbook.Worksheets("YourSheetName").range("A1").value = data_wbk1

data_wbk? seem to be Strings so it can be stored as is in the cell.


Read from workbook->
data_wbk1 = Thisworkbook.Worksheets("YourSheetName").range("A1").value

that makes sense. I will try after lunch with my dad. THanks! I will follow up if it work or not around 1-2 eastern. Thanks again! thanks fluff as well! I will let you both know the result

 

[jordanburch]

"As I understand it the only way to do that is putting it inside a spreadsheet. does that make sense?"

Yes.

This is just to get you started.

Save to workbook->
Thisworkbook.Worksheets("YourSheetName").range("A1").value = data_wbk1

data_wbk? seem to be Strings so it can be stored as is in the cell.


Read from workbook->
data_wbk1 = Thisworkbook.Worksheets("YourSheetName").range("A1").value

ok thanks buddy again! Here is my update. I cant quite figure out the syntax can you please help? its not working when i try the import below.

Thanks,

Jordan

data_wbk2 = InputBox("Enter month I.E. 08-MAY20", Default:="08-MAY20")
'ThisWorkbook.Worksheets("Variables").Range("A1").Value = data_wbk2
'data_wbk3 = Right(data_wbk2, 5)
'ThisWorkbook.Worksheets("Variables").Range("A2").Value = data_wbk3
'data_wbk1 = "FY" & Right(data_wbk3, 2)
'ThisWorkbook.Worksheets("Variables").Range("A3").Value = data_wbk1
'Workbooks.Open ("K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\Monthly Suspense Recon\" & data_wbk1 & "\" & data_wbk2 & "\" & "CARS" & "\" & data_wbk3 & " " & "CARS Detail Transaction Lines.xls*")
'data_wbk4 = data_wbk1
'ThisWorkbook.Worksheets("Variables").Range("A4").Value = data_wbk4
'data_wbk5 = Left(data_wbk2, 2) & " " & "-" & " " & Mid(data_wbk2, 4, 3) & " " & "20" & Right(data_wbk2, 2)
'ThisWorkbook.Worksheets("Variables").Range("A5").Value = data_wbk5
'data_wbk6 = InputBox("Enter month Name I.E. YYYYMM:", Default:="202005")
'ThisWorkbook.Worksheets("Variables").Range("A6").Value = data_wbk6
'data_wbk7 = Left(data_wbk3, 3)
'ThisWorkbook.Worksheets("Variables").Range("A7").Value = data_wbk7
'data_wbk8 = InputBox("Enter Prior month Name I.E. APRIL:", Default:="APRIL")
'ThisWorkbook.Worksheets("Variables").Range("A8").Value = data_wbk8
'data_wbk9 = InputBox("Enter Prior month I.E. 07 - APR 2020", Default:="07 - APR 2020")
'ThisWorkbook.Worksheets("Variables").Range("A9").Value = data_wbk9
'data_wbk10 = InputBox("Enter month Name I.E. YYYYMM:", Default:="202004")
'ThisWorkbook.Worksheets("Variables").Range("A10").Value = data_wbk10

'    data_wbk1 = ThisWorkbook.Worksheets("YourSheetName").Range("A1").Value


Workbooks.Open ("K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\Source Files\AFT_GEFBS\" & ThisWorkbook.Worksheets("Variables").Range("A4").Value & "\" & ThisWorkbook.Worksheets("YourSheetName").Range("A3").Value & "\" & "*.xls*")
Application.ScreenUpdating = True

 

[Fluff]

its not working when i try the import below.

In what way (other than the fact it's all commented out)?

 

[jordanburch]

In what way (other than the fact it's all commented out)?

it says subscript out of range 9 and then it points to this line

Workbooks.Open ("K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\Source Files\AFT_GEFBS\" & ThisWorkbook.Worksheets("Variables").Range("A4").Value & "\" & ThisWorkbook.Worksheets("YourSheetName").Range("A3").Value & "\" & "*.xls*")

I assume I didnt put the new variables in there correctly. I commented out the above just to see what I needed to put in place of the data_wbk variable. I did test it and it is inserting the variable correctly into the variable sheet, but its just not grabbing it correctly.