473,320 Members | 1,845 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Importing text using the FileSystemObject

Hi,

I have a spreadsheet with a column of txt filenames. I'm trying to import the text from each file to the adjacent cell using VBA, so far without luck. My VBA skills are very basic, so any help would be greatly appreciated.

Thanks
Oct 15 '12 #1
11 3027
twinnyfo
3,653 Expert Mod 2GB
imranolas,

What code have you tried so far? Are you getting errors or just not the desired results?
Oct 15 '12 #2
twinnyfo,

I've played with the filesystemobject and managed to pull the text from a single file to a single cell. However, I'm just a little stuck with how I would pull all text files to their relevant cells.

I tried to make it work as a function as below, but I expect I was being a little naive.

Expand|Select|Wrap|Line Numbers
  1. Function filetext(filename As String)
  2.  
  3. Dim fso As New FileSystemObject
  4. Dim ts As TextStream
  5.  
  6. Set ts = fso.OpenTextFile(filename)
  7. filetext = ts.ReadAll
  8. ts.Close
  9. End Function
Oct 15 '12 #3
twinnyfo
3,653 Expert Mod 2GB
Was the code above successful? How big are your text files, or are they relatively small?

I know it is possible to insert values into particluar cells in Excel, so what have you tried so far?
Oct 15 '12 #4
The files are fairly small, only 4 or 5 lines each. The above code returns #VALUE!.

I know the method works if I specify exactly the filename and destination like so.

Expand|Select|Wrap|Line Numbers
  1. Sub filetext()
  2.  
  3. Dim fso As New FileSystemObject
  4. Dim ts As TextStream
  5.  
  6. Set ts = fso.OpenTextFile("C:\Text\A0001.txt")
  7.  
  8. ActiveSheet.Range("A1").Value = ts.ReadAll
  9.  
  10. ts.Close
  11.  
  12. End Sub
I just don't know how to do much more with it.
Oct 15 '12 #5
twinnyfo
3,653 Expert Mod 2GB
So, if I understand Post #1 correctly, you have a list of filenames in a particular column of a spreadsheet, and you want to take that filename, open it, and transfer the contents of that file, and paste it into the column adjacent to the filename?

I do believe you are much closer than you realize.

Now, one really easy way to do this would be to link the spreadsheet to your databse, to treat it as a table. However, if there are mutiple spreasheets, this would defeat the purpose of automating here. Also, based on your code provided, it appears that your Spreadsheet has already been opened and you are able to write to specific cells already? Again, I think you are very close.

If you are able to access the filenames, you simply need to keep track of which cell the name came from and then insert the text into the next column over. You must be sure that you take into account the full path and filename, so if the cell contains the filename "A0001.txt", at some point you must know that the filename you will use for your purposes will have to become: "C:\Text\A0001.txt".

There are some differences in your two listed sets of code, so let me try this:

Expand|Select|Wrap|Line Numbers
  1. Sub filetext(FileName as String, CellRange as String) 
  2.     Dim fso As New FileSystemObject
  3.     Dim ts As TextStream
  4.     Set ts = fso.OpenTextFile("C:\Text\" & FileName)
  5.     ActiveSheet.Range(CellRange).Value = ts.ReadAll
  6.     ts.Close
  7. End Sub
The key here will be establishing the correct CellRange. If your Filenames are in column A and you want to write into column B, then CellRange will be "B1", "B2"... etc., based on the source of the FileName.

You may need to play with the code and validate that you are pulling the proper filenames, but I think you are almost there!
Oct 15 '12 #6
twinnyfo
3,653 Expert Mod 2GB
There are also ways to open the Excel Application from within VBA, and make direct updates. This would allow you to cycle through that first column of file names, and as long as the filename is not null, add the contents of the file.
Oct 15 '12 #7
You understand correctly. Although, I don't actually require this to be part of a database.

How would I apply this code to every value in the column?

Do I need to cycle the through the column like you've suggested? As this was my initial thought.

Thanks, I appreciate your help with this.
Oct 15 '12 #8
twinnyfo
3,653 Expert Mod 2GB
If you can hold on for a few minutes, I'll try to throw together some rough code for you...
Oct 15 '12 #9
twinnyfo
3,653 Expert Mod 2GB
imranolas,

OK - did a little testing and tweaking and this should get you going down the right road:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub UpdateExcelText()
  5. On Error GoTo EH
  6.     'Declare your variables
  7.     Dim xlApp As Excel.Application
  8.     Dim xlWB As Excel.Workbook
  9.     Dim xlWS As Excel.Worksheet
  10.     Dim intRow As Integer
  11.     Dim strFileName As String
  12.     Dim fso As New FileSystemObject
  13.     Dim ts As TextStream
  14.  
  15.     'Open Excel and get to the worksheet
  16.     Set xlApp = New Excel.Application
  17.     xlApp.Visible = True
  18.     Set xlWB = xlApp.Workbooks.Open("C:\Path\FileContents.xlsx", , False)
  19.     Set xlWS = xlWB.Worksheets("SheetName")
  20.  
  21.     intRow = 1
  22.     Do While Not xlWS.Range("A" & intRow).Value = ""
  23.         'Find the Filename
  24.         strFileName = xlWS.Range("A" & intRow).Value
  25.  
  26.         'Borrowing some of your code.....
  27.         Set ts = fso.OpenTextFile("C:\Path\" & strFileName)
  28.         xlWS.Range("B" & intRow).Value = ts.ReadAll
  29.         ts.Close
  30.         intRow = intRow + 1
  31.     Loop
  32.     xlWB.SaveAs "C:\Path\FileContents - Update.xlsx"
  33.     xlApp.Quit
  34.     Set xlApp = Nothing
  35.     Exit Sub
  36. EH:
  37.     MsgBox Err.Number & Err.Description
  38.     Exit Sub
  39. End Sub
  40.  
Of course, there are assumptions concerning the location of the filenames in the Spreadsheet, as well as file names and sheet names.

Hope this hepps!
Oct 15 '12 #10
Oh wow! That's brilliant, thanks. I have to confess I had another attempt before seeing your post and was successful.

You were right that I was close. Turns out it was a couple of rogue double quotes in the filenames that was causing me trouble.

Thanks again for your help.
Oct 16 '12 #11
twinnyfo
3,653 Expert Mod 2GB
Glad we could help. Your post also gave me a couple extra tools for my kit bag, as well!

Warm regards!
Oct 16 '12 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Scott MacLean | last post by:
I'm pulling my hair out over this one. I've got a web page that I need to have write to another server across the network. The site in question does NOT run in anonymous mode, that is, the user is...
1
by: DS | last post by:
Anyone know about importing text from notepad, using a Pocket PC into Access on a Desktop then back again? Thaks DS
3
by: Praetorian Prefect | last post by:
Code will not work in XP. Any ideas? Public Sub GetData() ' Declare variables Dim InputDir, ImportFile As String, tblName As String, strsql As String InputDir = ("f:\") ImportFile =...
1
by: Bishman | last post by:
Hi, I have been trying to get text to scroll smoothly accross a windows form using GDI+ and a timer. Changing the position of the text by a configurable amount and calling invalidate to force a...
1
by: Kosmos | last post by:
Hey guys, I'm fairly familiar with access by now, but I've never worked with importing text into an access database. I have some fairly large text files (lets say, for example, a folder of 20 text...
3
by: nskclr | last post by:
Hi I add a text using document.selection.createRange(). I want to add a text to the bottom of another text - similar to what we do in HTML. <p> xyz </p> <p><div...
5
by: dad59 | last post by:
I've been fighting this for 3 days. In an existing domain we have had a win2k3 Domain controller and 10 workstations. Website has been on this server and finally getting to move it to a brand new...
1
by: aconti74 | last post by:
Hello I am new to vba programming/coding. I am writing a program that goes through a directory of text files and imports them into the database. The problem is eventually the database gets to big...
0
by: Basha J P M | last post by:
I am beginner in python. I am working through the tutorial examples from http://www.swig.org/ and have run into some problems. I took the following command instructions from the tutorial on...
4
by: research_stuff | last post by:
I have researched unhiding text using Javascript. I found examples unhiding text boxes. I need to unhide text when a radio button has been selected. Can someone point me to an example? ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.