Firstly, I'm a beginner at VBA but I seem to get by on integrating already made codes and getting things
to work at the moment.
I have a specific solution I require but haven't managed to figure out exactly how to do
this effectively - it would be great if you could help?
Requirements as follows:
I have a single Excel
workbook which will be used by several users/machines and I want to have a macro which:
1 - Asks user where the import files are located in a browser type window (all will be in same folder so this only needs to
be done once, but the overall location of this folder may change depending on the user hence the need to ask this)
Another option would be to encourage the user to always keep the import folder in the same directory where the Excel file is
saved but then I would prefer the code to link to /IMPORT from the relative Excel saved location rather than
C/Username/Documents/IMPORT for example as multiple users will be working on this s/sheet.
2 - Import each specific file to the correct worksheet (detailed below), clearing existing data
and pasting the new import data from Cell D5 onwards (I have data which cannot be removed in columns A-C and rows 1-4)
3 - Save the workbook.
To clarify, I want to import the following files into the worksheets indicated. The
filenames will not change and they all be located in the same folder.
Textfile1.txt --> Worksheet1
Textfile2.txt --> Worksheet2
Textfile3.txt --> Worksheet3
CSVFile1.csv --> Worksheet4
CSVFile2.csv --> Worksheet5
CSVFile3.csv --> Worksheet6
I have the code for choosing the folder, but this code then imports all text files
contained within this folder to 1 worksheet. I want to extend the ability of this to work for the above.
on this would be much appreciated.
p.s. this isn't my own code, it was posted
from someone else on here (http://www.ozgrid.com/forum/showthre...t=78498&page=1)
Function GetFolder() As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = Application.DefaultFilePath
If .Show -1 Then Goto NextCode
sItem = .SelectedItems(1)
GetFolder = sItem
Set fldr = Nothing
Dim myDir As String, fn As String, ff As Integer, txt As String, a()
Dim x, i As Long, n As Long, b(), t As Long
myDir = GetFolder()
fn = Dir(myDir & "*.gpc")
Do While fn ""
ff = FreeFile
Open myDir & "" & fn For Input As #ff
Do While Not EOF(ff)
Line Input #ff, txt
x = Split(txt, ",")
n = n + 1
Redim Preserve a(1 To n)
a(n) = x
With ThisWorkbook. Sheets(1)
. Cells(t + 2, 3).Value = fn
For i = 1 To n
.Cells(i + t + 1, 4).Resize(, UBound(a(i)) + 1).Value = a(i)
Erase a: t = t + n + 0: n = 0
fn = Dir()
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines