Excel " symbol in a text string

I am creating a VBA to open multiole CSV files and process the contents.

I am using excel to generate the VBA code then pasting it into excel's VBA

I'm running into problems with the " symbol

I need to be able to enter this text field:

Workbooks.OpenText Filename:="X:\Projects\Data\01.txt"

However, I'd like to be able to have a column of numbers: 1,2,3,4...etc  and use

="Workbooks.OpenText Filename:="X:\Projects\Data\" & format(A1,"00") & ".txt"

However, the " symbol in the string prevents this from working.

I can use @ instead of " 

="Workbooks.OpenText Filename:=@X:\Projects\Data\" & format(A1,"00") & ".txt@"

Then use a text editor to replace @ with " but I'd like a more elegant solution as this will be used to add ~ ½ million data points.
Who is Participating?
hgj1357Connect With a Mentor Author Commented:
It's quad. this does it:

="Workbooks.OpenText Filename:=" & """" & "X:\Projects\Data\" & text(A1,"00") & ".txt" & """"

Also, I was using Access 'format' when in excel it is 'text'
Maybe it should be?

="Workbooks.OpenText Filename:=""X:\Projects\Data\" & Format(A1, "00") & ".txt"""
barry houdiniCommented:
If you want literal "s then you need to double them up, try

="Workbooks.OpenText Filename:=""X:\Projects\Data\" & format(A1,"00") & ".txt"""

regards, barry
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

hgj1357Author Commented:
Nope. Neither of these work.
barry houdiniCommented:
sorry hgj1357 I'm only a part-timer with VBA!

Jeez NBVC, we need some proper VBA people in here.......

regards, barry
So true, barry!  It's funny how we both came up with the exact same suggestion!  Just shows where we stand with VBA.  ROFL!
hgj1357Author Commented:
I should have spent more time on it before posting.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.