dubdurd
03-02-2021, 09:34 AM
Hi friends!
Newbie here, trying hard since days to find/learn what im looking for, with no success. You are my last hope. Probably not that hard to solve for some of you. I hope.
So, I have a master worksheet with several sheets (ie sheet1, sheet2, sheet3, etc). Daily we get source files (csv, sourcefile1_date, sourcefile2_date, etc) via email, which I download, open in text editor, copy manually and paste manually into the resp. sheets in the master workbook in the first empty cell in the list of each sheet.
My question: How can I manage with VBA, with a button on the first sheet (instructions sheet) to import the data from the csv files into their resp. sheet.
Meaning, when i have the sourcefile "sourcefile1_date", how can I tell excel to look for the name of that sourcefile, compare that name with the name of sheet1, sheet2, etc, until it matches the name from the sourcefile1_date and paste it in there in the first empty cell underneath the existing list. And do that for all selected files, putting their data into the correct sheets. The names of the sourcefiles and the sheets are the same, so excel basically just needs to compare the names to find the correct sheet for the sourcefile.
I have managed to import the main csv file into the first instructions sheet (i will post the code below), but telling excel via VBA to search for the name of the rest of the csv files, by comparing their names to the names of the sheets (sheet1, sheet2, sheet3, etc.) in order to insert it there into the first empty cell...please help!
Thanks a mill upfront, dear community
Sub ImportReport()
Dim fileToOpen As Variant
Dim fileFilterPattern As String
ChDrive "C:"
ChDir "C:\User\testfiles\"
Application.ScreenUpdating = False
fileToOpen = Application.GetOpenFilename()
If fileToOpen = False Then
'Input cancelled
MsgBox "No file selected"
Else
Workbooks.OpenText _
fileName:=fileToOpen, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=True, _
Comma:=False, _
Space:=False, _
Other:=False, _
FieldInfo _
:=Array(Array(1, 1), Array(2, 1)), _
TrailingMinusNumbers:=True
Columns("A:A").EntireColumn.AutoFit
Range("A1:B50").Select
Selection.Copy
Application.DisplayAlerts = False
ActiveWindow.Close
Application.DisplayAlerts = True
'Find first empty cell in column C
Dim FirstCell As String
Dim i As Integer
FirstCell = "C4"
Range(FirstCell).Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = "" Then
Exit Do
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
ActiveSheet.Paste
'Jump to the first empty cell in column C again
FirstCell = "C4"
Range(FirstCell).Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = "" Then
Exit Do
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End If
Application.ScreenUpdating = True
End Sub
Newbie here, trying hard since days to find/learn what im looking for, with no success. You are my last hope. Probably not that hard to solve for some of you. I hope.
So, I have a master worksheet with several sheets (ie sheet1, sheet2, sheet3, etc). Daily we get source files (csv, sourcefile1_date, sourcefile2_date, etc) via email, which I download, open in text editor, copy manually and paste manually into the resp. sheets in the master workbook in the first empty cell in the list of each sheet.
My question: How can I manage with VBA, with a button on the first sheet (instructions sheet) to import the data from the csv files into their resp. sheet.
Meaning, when i have the sourcefile "sourcefile1_date", how can I tell excel to look for the name of that sourcefile, compare that name with the name of sheet1, sheet2, etc, until it matches the name from the sourcefile1_date and paste it in there in the first empty cell underneath the existing list. And do that for all selected files, putting their data into the correct sheets. The names of the sourcefiles and the sheets are the same, so excel basically just needs to compare the names to find the correct sheet for the sourcefile.
I have managed to import the main csv file into the first instructions sheet (i will post the code below), but telling excel via VBA to search for the name of the rest of the csv files, by comparing their names to the names of the sheets (sheet1, sheet2, sheet3, etc.) in order to insert it there into the first empty cell...please help!
Thanks a mill upfront, dear community
Sub ImportReport()
Dim fileToOpen As Variant
Dim fileFilterPattern As String
ChDrive "C:"
ChDir "C:\User\testfiles\"
Application.ScreenUpdating = False
fileToOpen = Application.GetOpenFilename()
If fileToOpen = False Then
'Input cancelled
MsgBox "No file selected"
Else
Workbooks.OpenText _
fileName:=fileToOpen, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=True, _
Comma:=False, _
Space:=False, _
Other:=False, _
FieldInfo _
:=Array(Array(1, 1), Array(2, 1)), _
TrailingMinusNumbers:=True
Columns("A:A").EntireColumn.AutoFit
Range("A1:B50").Select
Selection.Copy
Application.DisplayAlerts = False
ActiveWindow.Close
Application.DisplayAlerts = True
'Find first empty cell in column C
Dim FirstCell As String
Dim i As Integer
FirstCell = "C4"
Range(FirstCell).Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = "" Then
Exit Do
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
ActiveSheet.Paste
'Jump to the first empty cell in column C again
FirstCell = "C4"
Range(FirstCell).Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = "" Then
Exit Do
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End If
Application.ScreenUpdating = True
End Sub