excel01noob
Board Regular
- Joined
- Aug 5, 2019
- Messages
- 93
- Office Version
- 365
- 2016
Hi
What is the best way to have the following:
-I have a .xlsm file that serves as template for other users and where I have VBA code written.
-In cell O19 I have the name and extension of the file we receive each month (Rawdata)
-my code is set in a way that if that raw data file name and extension is not open, there is a messagebox warning about it. If it is open, then code runs.
The rawdata file is sent via email and I would not want the users to save it, just open it from the email and then run the code.
But I am testing I see that this raw file is adding numbers to the file name and showing the Msgbox as if I don't have the file open (I have but not entirely with that name)
This is my code
++++++++++++++++++++++++++++
Sub checking_file_open ()
'For the Template workbook: TDATA is the defined format template
Dim wbTemp As Workbook
Dim wsTDATA As Worksheet,
Dim cName As String 'picks name and extension file required to check with Rawdata file
Set wbTemp = ActiveWorkbook
Set wsTDATA = wbTemp.Sheets("Data")
cName = wsTDATA.Range("O19")
'for the Rawdata workbook: FDATA for the data tab to use in the upload
Dim wbRaw As Workbook
Dim wsFDATA As Worksheet,
Set wbRaw = Nothing
On Error Resume Next
Set wbRaw = Workbooks(cName)
On Error GoTo 0
If wbRaw Is Nothing Then
MsgBox Range("O19") & " is not open!"
Else
'runs the rest of the code
End Sub
++++++++++++++++++++++++
The cell O19 is filled with
Monthly_report.cvs
I get the Msgbox when I have a file open as
Monthly_report (002).cvs
and I wanted to still run the rest of the code.
Anyway of doing that?
What is the best way to have the following:
-I have a .xlsm file that serves as template for other users and where I have VBA code written.
-In cell O19 I have the name and extension of the file we receive each month (Rawdata)
-my code is set in a way that if that raw data file name and extension is not open, there is a messagebox warning about it. If it is open, then code runs.
The rawdata file is sent via email and I would not want the users to save it, just open it from the email and then run the code.
But I am testing I see that this raw file is adding numbers to the file name and showing the Msgbox as if I don't have the file open (I have but not entirely with that name)
This is my code
++++++++++++++++++++++++++++
Sub checking_file_open ()
'For the Template workbook: TDATA is the defined format template
Dim wbTemp As Workbook
Dim wsTDATA As Worksheet,
Dim cName As String 'picks name and extension file required to check with Rawdata file
Set wbTemp = ActiveWorkbook
Set wsTDATA = wbTemp.Sheets("Data")
cName = wsTDATA.Range("O19")
'for the Rawdata workbook: FDATA for the data tab to use in the upload
Dim wbRaw As Workbook
Dim wsFDATA As Worksheet,
Set wbRaw = Nothing
On Error Resume Next
Set wbRaw = Workbooks(cName)
On Error GoTo 0
If wbRaw Is Nothing Then
MsgBox Range("O19") & " is not open!"
Else
'runs the rest of the code
End Sub
++++++++++++++++++++++++
The cell O19 is filled with
Monthly_report.cvs
I get the Msgbox when I have a file open as
Monthly_report (002).cvs
and I wanted to still run the rest of the code.
Anyway of doing that?