Hi All,
I am trying to open a csv file from MS Access. I am having to open excel then import the information so i don't lose the leading zeros. I have tried the below code but it behaves strangely. It work alternate times i try it. First it works then the next time i try it i get error message 'Range of object Global_Failed'. Does anyone have any ideas? Any help greatly appreciated.
Code:
Private Sub Command41_Click()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Add
xlApp.Visible = True
xlApp.Sheets("Sheet1").Select
xlApp.Range("A5").Select
xlApp.Sheets("Sheet1").Select
xlApp.Range("A1").Select
With xlApp.ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\elwelj01\Documents\004_Compensation.csv", Destination:=Range("$A$1"))
.Name = "004_Compensation"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub