Formatting problem with web query
I have a spreadsheet which, when opened for the first time, creates a web
query on the fly and imports some data into a worksheet. My problem is that the datatype for all the cells are automatically set to "General", but this causes some problems with formatting so I would like to be able to specify each columns data type separately. I can specify them in the worksheet, but the web query results will overwrite them. Is there some way to tell the web query only to overwrite the values, not the formatting specifications of the worksheet columns? I tried setting QueryTable.PreserveColumnInfo to true, but that gives an error: Run-time error '1004': Application-defined or object-defined error. I can set QueryTable.PreserveFormatting to true but that doesn't do anything. Here is the code that builds the web query: Private Sub Workbook_Open() Dim strURL As String Dim Q As QueryTable Dim strName As String Dim shtData As Excel.Worksheet Dim hlpData As Excel.Worksheet Dim ws As Worksheet Dim piv As PivotTable Dim i As Long Set shtData = Application.Worksheets("Data") Set hlpData = Application.Worksheets("Help") strName = hlpData.Cells(4, 5) If shtData.QueryTables.Count = 0 Then 'Not yet created web query strURL = hlpData.Cells(5, 5) & strName Set Q = shtData.QueryTables.Add(Connection:="URL;" & strURL, _ Destination:=shtData.Range("A1")) shtData.Unprotect With Q .RefreshStyle = xlOverwriteCells .WebDisableDateRecognition = True .Refresh 'Create named range called "DATA" for the resultset .ResultRange.Name = "DATA" End With shtData.Protect End If Set Q = Nothing Set shtData = Nothing Set hlpData = Nothing End Sub |
All times are GMT +1. The time now is 03:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com