biologyMajor
New Member
- Joined
- Apr 17, 2018
- Messages
- 2
Hi all,
I'm very new to excel macros and VBA, but I am trying to write a macro capable of importing all .csv files from a folder into separate worksheets of a workbook. I wrote a macro for ImageJ that is able to produce a .csv data file for each image processed (these have a fixed number of columns, but variable number of rows). With hundreds of images worth of data to analyze I thought developing a macro capable of performing data analysis would be the most appropriate course of action.
To begin with, I recorded the import of a .csv file to find the start point for my code, but after adding a folder select and trying to remove the file specification I get "Run-time error '1004': Application-defined or object-defined error" on the line
.Refresh BackgroundQuery:=False
I've tried changing it to True with no effect, and commenting out the line results in a loop of sheet additions. So far I've had no luck in searching for alternatives to Query Tables and am at a complete loss as to what is causing it to fail. Any help with coding or pointers would be greatly appreciated!
I'm running Excel 2010 on Windows 7.
Thanks in advance!
I'm very new to excel macros and VBA, but I am trying to write a macro capable of importing all .csv files from a folder into separate worksheets of a workbook. I wrote a macro for ImageJ that is able to produce a .csv data file for each image processed (these have a fixed number of columns, but variable number of rows). With hundreds of images worth of data to analyze I thought developing a macro capable of performing data analysis would be the most appropriate course of action.
To begin with, I recorded the import of a .csv file to find the start point for my code, but after adding a folder select and trying to remove the file specification I get "Run-time error '1004': Application-defined or object-defined error" on the line
.Refresh BackgroundQuery:=False
I've tried changing it to True with no effect, and commenting out the line results in a loop of sheet additions. So far I've had no luck in searching for alternatives to Query Tables and am at a complete loss as to what is causing it to fail. Any help with coding or pointers would be greatly appreciated!
Code:
Option Explicit
Sub ImportCSVData()
Dim wb As Workbook
Dim wbCSV As Workbook
Dim myPath As String
Dim myFile As Variant
Dim fileType As String
Dim i As Integer
'Get Target Folder Path From User
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select Source Folder"
.AllowMultiSelect = False
.Show
myPath = .SelectedItems(1)
End With
'Specify file type
fileType = "*.csv*"
'Target Path with file type
myFile = Dir(myPath & fileType)
'Add Target Workbook
Workbooks.Add
ActiveWorkbook.SaveAs Filename:= _
myPath & " Total Results.xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Set wb = Workbooks.Open(myPath & " Total Results.xlsm")
'Loop through each Excel file in folder
Do While myFile > 0
Worksheets.Add(Before:=Worksheets("Sheet1")).Name = "Image " & i + 1
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & myPath & "*.csv" _
, Destination:=ActiveSheet.Range("$A$1"))
.Name = myFile
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False 'Error occurs here!
End With
i = i + 1
Loop
'Message Box when tasks are completed
MsgBox "Result Import Complete"
End Sub
I'm running Excel 2010 on Windows 7.
Thanks in advance!