Macro help with IF statement.

[chriscmartin]

I am very new to macros and would like some help please. (I want this to run on Excel 2000 on an XP machine)

I have a macro that imports some data from a set file to my sells starting at A6.

My question is how do I tell my macro to check if there is data in cell A6 and if there is delete all data in cells a6:F1000 and if there is no data to skip to the next bit of the macro?

This is the macro I currently have.

Sub ImportGunData()
'
' ImportGunData Macro
'

'
Range("A6:D560").Select

Selection.QueryTable.Delete

Selection.ClearContents
Range("A6").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;L:\02 Application Development\Chris\StockCheckData.txt", Destination:= _
Range("$A$6"))
.Name = "StockCheckData"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(9, 2, 2, 1, 4, 2, 2, 9)
.TextFileFixedColumnWidths = Array(1, 5, 6, 1, 6, 6, 5)
.Refresh BackgroundQuery:=False
End With
End Sub

 

[chriscmartin]

With the current set up I get an error if there is no data in A6 to delete.

 

[VBA Geek]

[COLOR=#00007F][FONT=Courier]Sub[/FONT][/COLOR][COLOR=#000000][FONT=Courier] IfData()[/FONT][/COLOR]
[COLOR=#00007F][FONT=Courier]If[/FONT][/COLOR][COLOR=#000000][FONT=Courier] Len(Range("A6").Value) > 0 [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]Then[/FONT][/COLOR]
[COLOR=#000000][FONT=Courier]    Range("A6:F1000").Clear[/FONT][/COLOR]
[COLOR=#00007F][FONT=Courier]Else[/FONT][/COLOR]
[COLOR=#000000][FONT=Courier]    [/FONT][/COLOR][COLOR=#007F00][FONT=Courier]' ----------------------[/FONT][/COLOR]
[COLOR=#000000][FONT=Courier]    [/FONT][/COLOR][COLOR=#007F00][FONT=Courier]' next bit of macro[/FONT][/COLOR]
[COLOR=#000000][FONT=Courier]    [/FONT][/COLOR][COLOR=#007F00][FONT=Courier]' ----------------------[/FONT][/COLOR]
[COLOR=#00007F][FONT=Courier]End[/FONT][/COLOR][COLOR=#000000][FONT=Courier] If[/FONT][/COLOR]
[COLOR=#00007F][FONT=Courier]End[/FONT][/COLOR][COLOR=#000000][FONT=Courier] [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]Sub[/FONT][/COLOR]

 

[chriscmartin]

Please excuse my stupidity but what would the entire macro code be? I have tried adding that bit in at the beginning but it just shifts the cells to the right and inserts the data.

 

[VBA Geek]

if you have data in cell A6 you want to clear the range a6:f1000 and if no data then you want to import the text file?

 

[chriscmartin]

Sorry its wasn't so clear what I want to do,.

If there is data in A6 then clear a6:F1000, then no matter whether that was a yes or no I want it to import the file. basically clear out the old data if its still there and import the new.

 

[VBA Geek]

ok then just clear the range a6:f1000 at the beginning, you need not an IF

Range("A6:F1000").Clear

then the rest of your code

 

[chriscmartin]

Thank you very much for your help it works perfectly.

 

[vds1]

Try the below code, you have to clear the data before connection..

Sub ImportGunData()
'
' ImportGunData Macro


Range("A6:F1000").Clear


Range("A6:D560").Select
Selection.QueryTable.Delete
Selection.ClearContents


Range("A6").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;L:\02 Application Development\Chris\StockCheckData.txt", Destination:=Range("$A$6"))
    .Name = "StockCheckData"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = False
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFileStartRow = 1
    .TextFileParseType = xlFixedWidth
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(9, 2, 2, 1, 4, 2, 2, 9)
    .TextFileFixedColumnWidths = Array(1, 5, 6, 1, 6, 6, 5)
    .Refresh BackgroundQuery:=False
End With
End Sub