Add a string to a range and write it to a text file using VBA

[Moreco]

I have a text file that has 65 columns and 1836 rows.

I need to:

* Open the text file for the given path

* Create a vba code in excel that writes a string before each cell; (Example: "Insert into channel_data ("here have the cell information")"

* Place the entire workbook with the added information into a different text file

* Save and close the text file

I need the above information because my ultimate goal is to place all this data into mySQL.

Thank you so much for helping me.

 

[macropod]

Hi Moreco,

You can't place an Excel Workbook into a text file. You can save one as a delimited (tab or comma) text file, however. If there is more than once column of data per record, how do you want the records delimited? Do you want a header row and, if so, is the header in the workbook?

The code to pre-pend each cell with a text string is trivial, but do these cells have data only, or do some have formulae?

The code to save save the workbook to a specific folder, or a user-selected one is also simple. Do you want the user to select the path, or do you want to hard-code it?

Cheers

[MS MVP - Word]

 

[SkipVought]

Moreco,

You can concatenate a string before the value in each cell quite easily on the sheet, by using the CONCATENATE function or a formula like [tt]="Insert into channel_data"&A2[/tt].

Turn on your macro recorder and record doing each of the things that you want to do.

Post back with your recorded code if you need further help.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[CautionMP]

Moreco,

Sub Test01()
  Dim lngRow As Long, lngColumn As Long
  Dim intFile As Integer
  
  intFile = FreeFile
  Open "C:\OutputSQL.txt" For Output As #intFile
  
  For lngRow = 1 To 1836
    'Write SQL prefix
    Print #intFile, "Insert into channel_data (";
    'write the values seperated by commas
    For lngColumn = 1 To 65
      Print #intFile, ActiveSheet.Cells(lngRow, lngColumn); ",";
    Next lngColumn
    'Write the SQL affix
    Print #intFile, ")"
  Next lngRow
  Close #intFile
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[chandlm]

How about this?

Sub Addstr()
Dim textline As String
Open "C:\book1.txt" For Input As #1 'Open file to read from
Open "c:\book2.txt" For Output As #2 'Open file to write to

Do While Not EOF(1)
Line Input #1, textline
Print #2, "Insert into channel_data (" & textline & ")"

Loop
Close #1
Close #2

End Sub

Matt

 

[Moreco]

Thank you so much for all your help. I was able to accomplish my task.