Query table opens Excel file when it’s not done yet

I am working on an application that reads a large tab delimited text file and returns an excel file with multiple sheets with the same data.

I have been able to create the file successfully. However, I have come across a problem. When I import the file into a newly-created excel file the file opens when the first file is imported. I don’t want that to happen, I want it to be opened by the user when all the files have been successfully imported.

The interesting thing is that this didn’t happen when I was debugging the application at first. So I am baffled as to why it’s happening now. Could it be because I am missing a member for the Query table?

Can you please help me? Thanks!

Function:

Private Sub createExcel(pagina)

Dim hoja = 1

Dim excel As Excel.Application = New Microsoft.Office.Interop.Excel.Application()

If excel Is Nothing Then

MessageBox.Show("Error. Excel 2016 no está instalado en esta computadora", "Error")

Exit Sub

End If

Dim iSheetsPerBook As Integer

Dim misValue As Object = System.Reflection.Missing.Value

Dim xlWorkBook As Excel.Workbook

Dim xlWorkSheet As Excel.Worksheet

excel.SheetsInNewWorkbook = pagina

For i = 1 To pagina

iSheetsPerBook = excel.SheetsInNewWorkbook

xlWorkBook = excel.Workbooks.Add(misValue)

excel.SheetsInNewWorkbook = iSheetsPerBook

Next

For x = 1 To pagina

Dim archivo = String.Format("{0}ArchivosExcelexcel{1}.txt", dondeEstaElArchivo, x)

xlWorkSheet = xlWorkBook.Worksheets(hoja)

Dim qt As QueryTable = xlWorkSheet.QueryTables.Add(Connection:=String.Format("TEXT;{0}", archivo), Destination:=xlWorkSheet.Range("$A$1"))

With qt

.Name = "Import"

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.RefreshStyle = XlCellInsertionMode.xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = False

.RefreshPeriod = 0

.TextFilePromptOnRefresh = False

.TextFilePlatform = 1252

.TextFileStartRow = 1

.TextFileParseType = XlTextParsingType.xlDelimited

.TextFileTextQualifier = XlTextQualifier.xlTextQualifierDoubleQuote

.TextFileConsecutiveDelimiter = True

.TextFileTabDelimiter = True

.TextFileSemicolonDelimiter = False

.TextFileCommaDelimiter = False

.TextFileSpaceDelimiter = False

.TextFileColumnDataTypes = GetColumnDataTypes(xlWorkSheet.Columns.Count)

.TextFileTrailingMinusNumbers = True

.Refresh(BackgroundQuery:=False)

End With

hoja += 1

Next

Dim saveDialog As New SaveFileDialog()

saveDialog.Title = "Por favor elija donde quiere guardar el archivo"

saveDialog.Filter = "Excel 2007-on (*.xlsx)|*.xlsx"

Dim respuesta = saveDialog.ShowDialog()

If respuesta = DialogResult.Cancel Or saveDialog.FileName = "" Then

Exit Sub

End If

Dim fileSave = saveDialog.FileName

xlWorkBook.SaveAs(fileSave)

xlWorkBook.Close(True, misValue, misValue)

excel.Quit()

releaseObject(xlWorkSheet)

releaseObject(xlWorkBook)

releaseObject(excel)

End Sub

submitted by /u/betogm
[link] [comments]

Leave a Reply