• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 555
  • Last Modified:

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.
0
hgj1357
Asked:
hgj1357
  • 3
  • 2
  • 2
1 Solution
 
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
 
hgj1357Author Commented:
Nope. Neither of these work.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
hgj1357Author 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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now