Hi All,
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.
Any help on this would be much appreciated.
Thanks,
Rav
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)
VB:
Private Sub GetFiles_Click()
test
End Sub
Function GetFolder() As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = Application.DefaultFilePath
If .Show <> -1 Then Goto NextCode
sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function
Sub test()
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
Loop
Close #ff
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)
Next
End With
Erase a: t = t + n + 0: n = 0
fn = Dir()
Loop
End Sub
If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines
