Help to generate QR Code

[lookforsmt]

Thanks arnelgp and everyone for the inputs and suggestions, unfortunate i am too getting the same error mentioned in post #17 by ridders.

 

[arnelgp]

I dont know if this will make a difference. I am now clearing thw clipboard on each copy paste of the code.

 

[lookforsmt]

Thanks arnelgp for the revised db.

When i hit the form "sample QR save to Table" it generated a folder QR Image in the same location of db and created QR codes as images for the nos of data available in table MotorRun

But strange thing happened after i closed the db and reopened i got the at 2errors which is attached in post #17

I am not sure if i have done any modification. but to be on the safe side i re-uploaded the db but it still gives me the same error.

Also, instead of images can it be pulled via report.

 

[June7]

Tried it and now back to error on .Sheets(12).qrtopic line.

What exactly is qrtopic?


If you want to write your own code, here is a tutorial on the logic https://www.thonky.com/qr-code-tutorial/introduction. Maybe I could figure this out if I had a year to spare. One thing I did get from it is the Version number in cell B3 of the Input sheet can be used to determine the width and height of the range of cells for the code block. Now exploring code in another Excel workbook that would manipulate the QRCode workbook and run the CopyPicture code.

 

[arnelgp]

Here i made a much simpler sample.
Yiu can view the report motorrun on print and report view.

 

[June7]

Still get the error. Modified code below works for me.

Sub CreateQRCode()
Dim chtO As ChartObject
Dim x As Integer
With gxlWB
    .Sheets(1).textbox1.Value = Form_Form1.txtSample & ""
    .Sheets(1).textbox1_change
    .Sheets(1).CommandButton1_Click
    While gxlApp.CalculationState <> 0 'xlDone
        DoEvents
    Wend
    .Sheets(2).CommandButton1_Click
    While gxlApp.CalculationState <> 0
        DoEvents
    Wend
    x = Choose(.Sheets(1).Range("B3"), 21, 25, 29, 33, 37, 41, 45, 49, 53, 57, 61, 65, 69, 73, 77, 61, 85, 89, 93, 97, 101, 105, 109, 113, 117, 121, 125, 129, 133, 137, 141, 145, 149, 153, 157, 161, 165, 169, 173, 177)
    .Sheets(2).Range(.Sheets(2).Cells(2, 2), .Sheets(2).Cells(1 + x, 1 + x)).CopyPicture appearance:=xlScreen, Format:=xlBitmap
    Set chtO = .Sheets(2).ChartObjects.Add(1, 1, 200, 200)
End With
With chtO
    .Chart.Paste
    .Chart.Export FileName:=CurrentProject.Path & "\test.bmp", FilterName:="BMP"
    .Delete
End With
End Sub

 

[isladogs]

arnelgp
After specifying a path, I now get this error when testing your new sample db & no QR code is created

The code in the xlsm file created in your earlier posts is password protected. The file properties say its author is Johan Shoemann. Do you have the password?

june7
Which of arnel's samples have you modified to get this working with that code?

 

[June7]

The one in post 22.

 

[isladogs]

Hi June

Just tried adding your code to the sample from post #22.
Being a bit clueless here...
Am I right in thinking I just replace the GenQRCode with CreateQRCode in Form1as below

Private Sub cmdGenerate_Click()
    If Trim(Me.txtSample & "") <> "" Then
        'GenQRCode Me.imgQRCode, Me.txtSample
        CreateQRCode
    End If
End Sub

... or should be used in the Sample QR Code ... form?

Anyway, I tried that and got a compile error on the line Dim chtO As ChartObject. Fixed by adding the Excel reference library.

I tried running that from the button in each of the above forms

Using Form1 - error 91 on this line:

   .Sheets(1).textbox1.Value = Form_Form1.txtSample & ""

Using Sample QR code ... form, the code runs for ages before eventually giving an automation error

TRouble is I've no real idea how the code is meant to work so I can't troubleshoot. So unless you can tell me I've used your code incorrectly I'm going to give up on this & go back to my own projects.

 

[June7]

See if this version works. I am no longer getting errors.

 

[isladogs]

Hi June

When I opened the form, the code broke at the line

If Dir(CurrentProject.Path & "\QRCodeImages", vbDirectory) = "" Then

After continuing it created the folder correctly
However when I opened the form again, it again broke there despite the folder now existing.

I decompiled the database & the problem was fixed

I then entered a web address & successfully generated the BMP file below in the new folder. Disappointingly it didn't appear on the form but I've now added it manually.

The good news is I scanned the image with a QR reader app & it successfully opened the website - it worked!
Hopefully it will also work if others try to scan it from this post

If not, try downloading the attached file

 

[lookforsmt]

HI! all, i want to thank each and everyone for their feedback and updates.
Hope there is a solution.

regards

 

[arnelgp]

the good news is ive found yet another excel qr generator. It doesnt use vba. The inly vba is the one i added to oroduce the bitmap. Will post sample tomorrow with excel and no password.

The inly catch is ut will only accept 15 chard (enough for real world app).

 

[isladogs]

the good news is ive found yet another excel qr generator. It doesnt use vba. The inly vba is the one i added to oroduce the bitmap. Will post sample tomorrow with excel and no password.

The inly catch is ut will only accept 15 chard (enough for real world app).

Arnel
If it only accepts 15 characters, it won't be any use for creating QR codes from URLs which does limit it's usefulness.

As already stated, June's last update worked for me as shown by the QR BMP file I uploaded

 

[arnelgp]

I think the op want it for his product abd scanning. So its adequate. Besides this one has no vba on it.

 

[June7]

Decompile - first time I've ever had to do that. I got runtime error on that line until added the vbDirectory parameter. Guess VBA held onto that error flag even after corrected and compiled. I forgot to fix the Image control ControlSource for the folder path. Added Me.Refresh to the form code. Removed the line to make Excel visible and now works. Decompile/compile must have fixed several issues. Replaced file in post 30.

And now the Version number is recalculating and the image saved is complete. Wonder why that was an issue before.

The image seems a little fuzzy in Image control although looks sharp in Windows Explorer preview.

Did a test with 15 characters, calculated as Version 3 which is a 29x29 block.

 

[isladogs]

June
Excellent - it worked for me as well with no errors
QR Code now appears on form & both reports & is recognised by my scanner.
I've reduced the image size on arnel's statement report & its now sharp enough in my view

Did you try scanning the QR image I uploaded yesterday?

Thanks for your time & effort getting this to work.
I haven't looked at the code yet as I have my own issues with geolocation service and IE which is taking up too much of my time

 

[Uncle Gizmo]

Hopefully it will also work if others try to scan it from this post

Yes, I scanned the QCode and it worked!

Interesting website, that!

 

[isladogs]

Yes, I scanned the QCode and it worked!

Interesting website, that!

Hi Tony
Haven't seen you on the forum for a while.

It was a good excuse for a shameless bit of self publicity!

Have you looked at my current thread - Getting current location in VBA?
https://www.access-programmers.co.uk/forums/showthread.php?t=299592
I'd be interested in what results you get if you would like to try it

 

[June7]

I would have to download an app onto my phone to scan.