topic Re: bad file or number error while running the macro.. in QlikView

bad file or number error while running the macro..

dhasharadh — Fri, 15 Nov 2024 23:54:40 GMT

Hi, every thing was fine until we did this small change in the macro, now there is a error.

Dear experts @marcus_sommer @Gysbert_Wassenaar @Miguel_Angel_Baeyens @ can any of you look into this error please.

the below macro generate 2 text files and we are trying to place the generated text files in the \\qlkvapps001.flip.onmicrosoft.com\WUR folder for which we added few lines line Dim FilePath and that's it.

am getting bad file name or number error and this line is getting highlighted.

Set MyFile = fso.CreateTextFile(strFile)

Full Macro :

Sub export
ActiveDocument.clearAll true
curMonth = Month( DateAdd("M",-1, Date))
if curMonth<10 then curMonthS="0" & CStr(curMonth) else curMonthS=CStr(curMonth)
IF curMonth=1 OR curMonth=3 OR curMonth=5 OR curMonth=7 OR curMonth=8 OR curMonth=10 OR curMonth=12 Then
noofdays=31
else noofdays=30
end if

Dim wellNames(2)
wellNames(0)="Eschenfelden US"
wellNames(1)="Bierwang US"

Dim FileNames(2)
FileNames(0)="Esch"
FileNames(1)="Biew"

Dim FilePath(1)
FilePath(1) = "\\qlkvapps001.flip.onmicrosoft.com\WUR"

Set fso = CreateObject("Scripting.FileSystemObject")

For i = 0 to 1
ActiveDocument.Fields("%KEY_REPORT").Select WCONHIST,WCONINJH
ActiveDocument.Fields("GASMONTH").Select Year(Date)&"-"&curMonthS
ActiveDocument.Fields("SP.LANGBEZ_SSO_SPEICHER").Select wellNames(i)

strFile = FilePath(i) & FileNames(i) & "_UGS_01_" & curMonthS & "_" & noofdays & "_" & curMonthS & "_" & Year(Date) & ".txt"

Set MyFile = fso.CreateTextFile(strFile)

set table = ActiveDocument.GetSheetObject( "TB02" )
for RowIter = 0 to table.GetRowCount-1
set cell = table.GetCell(RowIter,0)
MyFile.WriteLine(cell.Text)
next
MyFile.Close
Next
ActiveDocument.clearAll true
ActiveDocument.Save
ActiveDocument.GetApplication.Quit

End Sub

BR,

Dhasahradh

Re: bad file or number error while running the macro..

marcus_sommer — Mon, 28 Sep 2020 06:23:36 GMT

Put \\qlkvapps001.flip.onmicrosoft.com\WUR within the windows explorer on the machine and with the user which executes the macro - does it work and you could see the folder and it's content? If yes, could you add some new content and change and delete it afterwards (just a new txt with test or similar as content)?

- Marcus

Re: bad file or number error while running the macro..

dhasharadh — Mon, 28 Sep 2020 06:55:18 GMT

Thank you Marcus,

Yes am able to access the path manually and also able to edit and delete the text file which is pasted in the above location.

i just tested all this manually. is that bad file name or number error is due to any syntax error...??

Re: bad file or number error while running the macro..

marcus_sommer — Mon, 28 Sep 2020 09:03:57 GMT

I suggest to use a msgbox to output the final full-path because nearly all (variable) parts are dynamically and some may return a wrong/unexpected result. Especially take a look to:

FilePath(i)

which has only one defined value but is called twice with i = 0 and i = 1 ...

- Marcus

Re: bad file or number error while running the macro..

dhasharadh — Mon, 28 Sep 2020 09:51:24 GMT

did not get it Marcus, can you please elaborate bit more with an example ...

Re: bad file or number error while running the macro..

marcus_sommer — Mon, 28 Sep 2020 13:13:18 GMT

With:

Dim FilePath(1)
FilePath(1) = "\\qlkvapps001.flip.onmicrosoft.com\WUR

you define FilePath as array and set only a single value. Now you ran a loop with i as loop-counter and 2 iterations and calls with it the FilePath:

For i = 0 to 1

...

strFile = FilePath(i) & FileNames(i) & "_UGS_01_" & curMonthS & "_" & noofdays & "_" & curMonthS & "_" & Year(Date) & ".txt"

...

but there is only one value for i = 1, i = 0 has no value and will be probably NULL and therefore there will be no valid path in this case. Just change this array to a normal variable respectively a const.

- Marcus