topic Re: Macro to replace file format in QlikView

Macro to replace file format

Thu, 20 Nov 2014 12:11:26 GMT

Hi,

I am trying to replace file format from xlsx to xls using macros. Since I hardly know VBA, I need help.

the code I am using is:

Private Sub CommandButton1_Click()

Dim myfile As String

With Application.FileDialog(msoFileDialogFolderPicker)

.AllowMultiSelect = False

If .Show = -1 Then

folderName = .SelectedItems(1)

End If

End With

myfile = vFile

Do While myfile <> ""

Workbooks.OpenText Filename:= myfile

'save as excel file

ActiveWorkbook.SaveAs Filename:=folderName & "\" & Replace(myfile, ".xlsx", ".xls")

Application.DisplayAlerts = False

ActiveWorkbook.Close

Application.DisplayAlerts = True

myfile = Dir

Loop

End Sub

Here, vFile is the variable that contains entire file path, which is coming from a for each loop in the main script. I don't know if we can call this variable here.

Re: Macro to replace file format

Peter_Cammaert — Thu, 20 Nov 2014 12:19:04 GMT

Replace

myFile = vFile

with

SET var = ActiveDocument.GetVariable("vFile")

myFile = var.GetContent.String

Best,

Peter

Re: Macro to replace file format

Thu, 20 Nov 2014 12:21:12 GMT

Thanks for the reply Peter. Could you also pls explain, how to call the macro and what would be it's name?

Also, there comes a pop saying Macro parse failed. Any help on this?

Re: Macro to replace file format

CELAMBARASAN — Thu, 20 Nov 2014 12:28:41 GMT

Sometimes Qlik VBA functions works without Set keyword

var = ActiveDocument.GetVariable("vFile")

myFile = var.GetContent.String

Re: Macro to replace file format

Peter_Cammaert — Thu, 20 Nov 2014 12:31:23 GMT

Macros are called by way of a QlikView concept called "Action". An action is executed whenever triggered by an Event.

Actions can be added to certain QV objects that are not by default click-sensitive, like a Text box or a Button. In Object Properties you'll find a tab called Actions where you can assign o,ne or more actions to be executed in sequence whenever this object is clicked.

My guess is that you want to add a button to your sheet, that calls this macro when clicked. To accomplish this, do the following:

Add a button object and open its properties
On the General tab, change the button text into something that explains its purpose
On the Actions tab, click Add, select Action Type = External and in the list box on the right select Run Macro and click OK.
In the Macro Name-field that pops up, enter the macro text between Private Sub and the parentheses. That's the name of the macro.
Close the dialogs and click the button to test its operation.

Best,

Peter

Re: Macro to replace file format

Thu, 20 Nov 2014 13:24:41 GMT

This is what I typed in the Run Macro box:

Private Sub CLICK()

Dim myfile As String

With Application.FileDialog(msoFileDialogFolderPicker).AllowMultiSelect = False

If .Show = -1 Then

End If

End With

SET var = ActiveDocument.GetVariable("vFile")

myFile = var.GetContent.String

Do While myfile <> ""

Workbooks.OpenText Filename:= myfile

ActiveWorkbook.SaveAs Filename:= Replace(myfile, ".xlsx", ".xls")

Application.DisplayAlerts = False

ActiveWorkbook.Close

Application.DisplayAlerts = True

Loop

End Sub

But the code doesn't seem to work. Nothing happens.

Re: Macro to replace file format

Peter_Cammaert — Thu, 20 Nov 2014 13:33:27 GMT

The macro definition itself should go somewhere else. In the main Desktop menu, select Tools->Edit Module... The macro editor will open up. Paste the macro code in the window on the right, possibly after any definitions that are already present. When you click OK, the macro will be stored and definition will be complete. From now on, you can use this piece of VBA code by simply specifying its name.

Now go back to your Run Macro action and just enter the macro name in the Macro Name-field.

Re: Macro to replace file format

Anonymous — Thu, 20 Nov 2014 14:31:04 GMT

Try this this may help you

sub test

vFile="C:\temp\test.xlsx"

Set Obj_XLApp = CreateObject ("Excel.Application")

Obj_XLApp.Visible = false

Obj_XLApp.DisplayAlerts = false

save_as_File_Name=left(vFile,len(vFile)-1)

'msgbox save_as_File_Name

set Obj_XLTemplate = Obj_XLApp.Workbooks.open (vFile)

Obj_XLTemplate.saveas (save_as_File_Name)

Obj_XLTemplate.close

call sbDeletetingAFile(vFile)

Obj_XLApp.Visible = true

Obj_XLApp.DisplayAlerts = true

set Obj_XLTemplate=nothing

Set Obj_XLApp = nothing

end sub

Sub sbDeletetingAFile(sFile)

Set FSO = CreateObject("Scripting.FileSystemObject")

FSO.DeleteFile sFile, True

End Sub

Regards

Harsha