Attempting a complex import

limey

New Member
Joined
Nov 16, 2005
Messages
3
Hi there,

I am relatively new to VBA and venturing into unknown territory with a keen interest to learn.

I have on my PC many folders that are each named according to a workstations IP address. WIthin this folder there are 2 text files and a xls file that contain different hardware information.

What I need to accomplish is importing into an already existing workbook, filtered information from these files. The data is always in the same area in the xls file and in the 2 text files.

If anyone can help me get started I would greatly appreciate the assistance. I wouldn't want anyone to have to give me step by step, but learning by doing would be the best method here!

Cheers!
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Hi there limey, welcome to the board!

Can you give us a little more information here? I.e. will you always know the path to these folders named after the IP addresses? Where are these [IP] addresses kept, in the file to import to? Where should the data be imported to?

I think most importantly, it would help to understand your folder structure and how it must be integrated into code.
 

limey

New Member
Joined
Nov 16, 2005
Messages
3
Hi firefytr,

Thank you for your reply!

I have created a batch file that I use to connect to workstations with their IP's located in a text file, which runs some code to extract hardware and software info, this gets put into 2 text files and a xls file. In the batch I create the folder based on the workstation IP, the structure is constant and is organized as follows:

C:\Invemtory\*IP of Workstation*\
text1.txt
text2.txt
xlsfile.xls

In the xls file I require the text that is in the following cells (always the same range):

C28
C34
O29
O39
O68

The data is to be imported into a pre-defined worksheet. Each piece of data has a "home" in this spreadsheet. The Basic layout of the spreadsheet is:

A | B | C | D |
Workstation_IP | Extracted1 | Extracted2 | Extracted 3|

One caveat here is that the text in extracted 3 requires to be added as a comment.


I hope this sheds a little more light on this. Please let me know if you require anything else.

Cheers!
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
It sheds a lot more light, thank you, very good description.

I do have some more questions before going further:
1) Are the text files always named in this convention, with those names?
2) What line(s) do you need extracted from text1.txt?
3) What line(s) do you need extracted from text2.txt?
4) Where do you want the data from text1.txt? Extracted1?
5) Where do you want the data from text2.txt? Extracted2?
6) Is Extracted3 (or like) for all those cells of data from the Excel file? Do you want them seperated in any fashion?
7) What is the comment you need? From one of these data sources?
8) Will this data need to 'grow' as more info is extracted?
9) Do you want to choose the [IP] folder to import? Or do you want to automatically go through all [IP] folders?
10) Where is this 'pre-defined' worksheet? And what workbook is it in?
 

limey

New Member
Joined
Nov 16, 2005
Messages
3
RE: Complex Import

Hi firefytr,

Sorry for the delayed response. I have hopefully answered your questions below:

1) Are the text files always named in this convention, with those names?
Yes they always have this naming convention
2) What line(s) do you need extracted from text1.txt?
Line 3, Line 6, Lines 15-17
3) What line(s) do you need extracted from text2.txt?
All Lines
4) Where do you want the data from text1.txt? Extracted1?
Yes
5) Where do you want the data from text2.txt? Extracted2?
Yes
6) Is Extracted3 (or like) for all those cells of data from the Excel file? Do you want them seperated in any fashion?
Extracted 3 will contain the cell data from xls
Seperated by carriage return only, but imported as a Comment in a single cell for each IP Address.
7) What is the comment you need? From one of these data sources?
All cells from xls are to be imported as a comment.
8) Will this data need to 'grow' as more info is extracted?
If there is a way to get this data into a string than it will grow for each IP address (this is how they will be organized)
9) Do you want to choose the [IP] folder to import? Or do you want to automatically go through all [IP] folders?
Preferably to automatically go through, but would also like to experiment choosing the folder to import just for learning if possible.
10) Where is this 'pre-defined' worksheet? And what workbook is it in?
The pre-defined worksheet is called "Workstation Config" The workbook it is in is called "Devices.xls"

I hope this answers your questions. Please let me know if you require further clarification. Again thanks for your assistance with this!

Cheers!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,674
Messages
5,573,597
Members
412,537
Latest member
Mohamed_5966
Top