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.
LVL 2
hgj1357Asked:
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'
0
 
NBVCCommented:
Maybe it should be?

="Workbooks.OpenText Filename:=""X:\Projects\Data\" & Format(A1, "00") & ".txt"""
0
 
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
0
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.
0
 
barry houdiniCommented:
sorry hgj1357 I'm only a part-timer with VBA!

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

regards, barry
0
 
NBVCCommented:
So true, barry!  It's funny how we both came up with the exact same suggestion!  Just shows where we stand with VBA.  ROFL!
0
 
hgj1357Author Commented:
I should have spent more time on it before posting.
0
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.