Use a Piece of Data for File Name

[Guest]

Hi RBS,

Thank you again very much for your patience.

First of all, Line 79 is "ActiveWorkbook.SaveAs CSVDir & CSVFName,
xlCSVWindows, , , , False, xlNoChange".

Secondly, I find where the problem is, and I do not know how to solve it.
The problem is on the "CRYear".

In addition to using "CRYear" in "CSVFName", I also use it in "CSVDir".
CSVDir is the directory where the CSV file will be saved, and CSVFName is the
file name of a CSV file. The code that I posted has the CSVDir as "C:\".
Actually, it should be ""C:\" & CRYear". Due to business reason, we have CSV
files for different years. The CSV files are named combination of customer
name and the year for the CSV file (e.g. ABC2007.csv) and are saved in the
appropriate folder for the CSV year. The customer name and CSV year should
come from the input file (Workbook A). Apparently, the VBA does not like the
fact that CRYear is being used for various purpose.

I have tried to creating a new variable (DIRYear) and referencing the
DIRYear to a different cell. The code still does not work. I am still
getting the same error.

Is there a way around it? Thanks.

 

[RB Smissaert]

Sorry, you lost me a bit now.
You will have to get to the essential part of the problem.

RBS

 

[Guest]

Basically, the CSV file is named using the FName and CRYear (e.g.
"ABC2007.csv"). Both FName and CRYear are referenced to the cells in the
input file (Workbook A). In addition to using the CRYear as a portion of
file name, I also use it for the directory (CSVDir = "C:\" & CRYear & "\") so
that the CSV files will be saved to the proper directory/year.

The problem is, for some reason, the code does not like the fact that CRYear
is being used for 2 purposes. If I change the code for CSVDir to "C:\2007\"
from '"C:\" & CRYear & "\"', it would work and I would not get errors.
However, I can not hard coded to 2007 because not all CSV files will be for
2007.

I got the errors on Line 790:

ActiveWorkbook.SaveAs CSVDir & CSVFName, xlCSVWindows, , , , False, xlNoChange

On Line 790, "CSVDir" (based on the original code) is: "C:\" & CRYear & "\".

On Line 790, "CSVFName" (based on the original code) is: FName & CRYear &
".csv".

Again, thank you very much for your patience. I think we are almost there.
The problem is essential part of my code because we will produce 300 CSV
files and they need to be located properly. Please ask me if you need more
clarification.

 

[RB Smissaert]

The problem is, for some reason, the code does not like the fact that

CRYear
is being used for 2 purposes

That shouldn't be any problem.

The essential question is what file or folder string is produced when you
get an error?
So, what you do is this:
In the VBE do View, Immediate window

Then on a line just before your error line put:
Debug.Print "C:\" & CRYear & "\"

Come to look at it now you have 2 double quotes before C and after \
This should be only one. That might be it.

RBS

 

[Guest]

I don't think the problem is due to the double quotation because there are 3
pieces to the CSVDir ("C:\", CRYear and "\"). If it is a problem, which one
should I remove?

I did per your instructions, and I don't know what I should look for in the
Immediate window. Below is what I got from Immediate when I did the "Run to
Cursor" on the Debug.Print "C:\" & CRYear & "\" line.

[auto_open] <
[SetupFunctionIDs] <
[SetupFunctionIDs] >
[PickPlatform] <
[PickPlatform] >
[VerifyOpen] <
[VerifyOpen] > 1
[RegisterFunctionIDs] <
[RegisterFunctionIDs] >
[auto_open] TrueTrue

Then I got an error "Subscript Out of Range, and it indicated to line where
I have

CRYear = Year(.cells(2))

Thanks.

 

[RB Smissaert]

If it is a problem, which one should I remove?

Remove the very first one and the very last one.
You are building a string to make a folder path. This path has 3 bits:
"C:\"
your variable: CRYear
"\"
So you get "C:\" & CRYear & "\"
Why do you want an extra double quote before "C:\" or after "\" ?

"Run to Cursor" on the Debug.Print "C:\" & CRYear & "\" line.

Not sure what you are doing here.
Debug.Print should dump the result of "C:\" & CRYear & "\"
and I don't see that.


RBS

I don't think the problem is due to the double quotation because there are
3
pieces to the CSVDir ("C:\", CRYear and "\"). If it is a problem, which
one
should I remove?

I did per your instructions, and I don't know what I should look for in
the
Immediate window. Below is what I got from Immediate when I did the "Run
to
Cursor" on the Debug.Print "C:\" & CRYear & "\" line.

[auto_open] <
[SetupFunctionIDs] <
[SetupFunctionIDs] >
[PickPlatform] <
[PickPlatform] >
[VerifyOpen] <
[VerifyOpen] > 1
[RegisterFunctionIDs] <
[RegisterFunctionIDs] >
[auto_open] TrueTrue

Then I got an error "Subscript Out of Range, and it indicated to line
where
I have

CRYear = Year(.cells(2))

Thanks.

That shouldn't be any problem.

The essential question is what file or folder string is produced when you
get an error?
So, what you do is this:
In the VBE do View, Immediate window

Then on a line just before your error line put:
Debug.Print "C:\" & CRYear & "\"

Come to look at it now you have 2 double quotes before C and after \
This should be only one. That might be it.

RBS